Re: Error proof way to find first blank Row after Autofilter Range

Wow Tom!

I guess that I did asked for the Error-Proof method. I'll carefully study your code!

EagleOne

Tom Ogilvy <TomOgilvy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Public Function GetRealLastCell(sh As Work***)
Dim RealLastRow As Long
Dim RealLastColumn As Long
Set GetRealLastCell = Nothing
On Error Resume Next
RealLastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
RealLastColumn = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
Lookat:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
If Err.Number <> 0 Then _
Set GetRealLastCell = sh.Range("A1")
On Error GoTo 0
End Function

Sub FindNextEmpty()
Dim rng as Range
set rng = GetRealLastCell(Active***)
set rng = Active***.cells(rng.row,1)
with Active***
if .AutofilterMode then
set rng1 = .Autofilter.Range
set rng1 = rng1(rng1.count)
if rng1.row > rng.row then
set rng = rng1
end if
end if
End with
msgbox "Next blank row is " & rng.offset(1,0).row
End Sub
.

Loading