ExcelBanter - Error proof way to find first blank Row after Autofilter Range

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Error proof way to find first blank Row after Autofilter Range (https://www.excelbanter.com/excel-programming/392391-error-proof-way-find-first-blank-row-after-autofilter-range.html)

Error proof way to find first blank Row after Autofilter Range

2003/2007

Attempted:

With Filter:
wks.Cells(Rows.Count, "D").End(xlUp).Row = 184

After removing the Filter:
wks.Cells(Rows.Count, "D").End(xlUp).Row = 331

What can I use the make sure that I get 331 Filtered or Not Filtered?

What I want is the Row number of the first available totally-blank row
assuming that all previous rows are not blank.

I realize that I could filter for blanks and maybe that is an answer.

Prefer not to Filter A; Un filter A; Filter B; Un filter B; Filter A

TIA EagleOne

Error proof way to find first blank Row after Autofilter Range

Thanks!

I used it in one line:
wks.Autofilter.Range.Rows(wks.Autofilter.Range.Row s.Count).Row

"tissot.emmanuel" wrote:

wks

Error proof way to find first blank Row after Autofilter Range

Public Function GetRealLastCell(sh As Worksheet)
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(Activesheet)
set rng = Activesheet.cells(rng.row,1)
with ActiveSheet
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

--
Regards,
Tom Ogilvy

"tissot.emmanuel" wrote:

Hi,

Try this:

Sub TheLastRow()
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
If .AutoFilterMode Then
With .AutoFilter.Range
MsgBox .Rows(.Rows.Count).Row
End With
Else
MsgBox "No filter found"
End If
End With
End Sub

Regards,

Manu/

a Ã©crit dans le message de news:
...
2003/2007

Attempted:

With Filter:
wks.Cells(Rows.Count, "D").End(xlUp).Row = 184

After removing the Filter:
wks.Cells(Rows.Count, "D").End(xlUp).Row = 331

What can I use the make sure that I get 331 Filtered or Not Filtered?

What I want is the Row number of the first available totally-blank row
assuming that all previous rows are not blank.

I realize that I could filter for blanks and maybe that is an answer.

Prefer not to Filter A; Un filter A; Filter B; Un filter B; Filter A

TIA EagleOne

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 wrote:

Public Function GetRealLastCell(sh As Worksheet)
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(Activesheet)
set rng = Activesheet.cells(rng.row,1)
with ActiveSheet
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

Error proof way to find first blank Row after Autofilter Range

Yes you did, but if you know you will have an autofilter range that contains
the last used row in your sheet, then your one liner will be sufficient
whether the sheet is in filtermode or not. (rows hidden by the filter or not)

--
Regards,
Tom Ogilvy

" wrote:

Wow Tom!

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

EagleOne

Tom Ogilvy wrote:

Public Function GetRealLastCell(sh As Worksheet)
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(Activesheet)
set rng = Activesheet.cells(rng.row,1)
with ActiveSheet
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

Error proof way to find first blank Row after Autofilter Range

Tom,

I'll also post the following as a new item is case you will not be available this weekend.

Ran into this challenge:

Set CurrRng = wks.Range(Cells(DataStartRow, DataTopCol).Address, _
Cells(wks.Cells(Rows.Count, "B").End(xlUp).Row, _
DataTopCol)).SpecialCells(xlCellTypeVisible)l

The code above yields: (CurrRng.address)

If CurrRng.SpecialCells(xlCellTypeVisible).Cells(1).R ows.Count = 1 (one data item row)
Then $1:$67,$149:$149,$332:$65536

If CurrRng.SpecialCells(xlCellTypeVisible).Cells(1).R ows.Count 1 (data item rows)
Then $149

The only data row visible is 149

The VBA code, after the "SET," crashes with $1:$67,$149:$149,$332:$65536
but is OK with $149

My current workaround is:

DataRowsCount = CurrRng.SpecialCells(xlCellTypeVisible).Cells(1).R ows.Count
Set CurrRng = wks.Range(Cells(DataStartRow + IIf(DataRowsCount = 1, 1, 0), _
DataTopCol).Address, Cells(wks.Cells(Rows.Count, "B").End(xlUp).Row, _
DataTopCol)).SpecialCells(xlCellTypeVisible)

What causes the different results?

I really appreciate your thoughts and time

EagleOne

Tom Ogilvy wrote:

Yes you did, but if you know you will have an autofilter range that contains
the last used row in your sheet, then your one liner will be sufficient
whether the sheet is in filtermode or not. (rows hidden by the filter or not)