0

I want to be able to find the row number of the first blank row under a filtered table. I was using this code, but it finds the final line of the filtered table.

.Range("A1").End(xlDown).Row
Community
  • 1
  • 1
KOM
  • 511
  • 1
  • 6
  • 9
  • Would [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) help? – Siddharth Rout Aug 29 '14 at 11:23
  • @SiddharthRout I think your answer here would be more like it: http://stackoverflow.com/questions/12586848/get-last-row-from-filtered-range But I posted an answer anyway :) – CaptainABC Aug 29 '14 at 11:54

1 Answers1

1

Here you go:

  Dim HeaderRow As Long, LastFilterRow As Long, Addresses() As String
  On Error GoTo NoFilterOnSheet
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    Addresses = Split(.Range((HeaderRow + 1) & ":" & LastFilterRow). _
                      SpecialCells(xlCellTypeVisible).Address, "$")
    GetFilteredRangeBottomRow = Addresses(UBound(Addresses))
    FirstBlankRow = GetFilteredRangeBottomRow + 1
    MsgBox FirstBlankRow
  End With
NoFilterOnSheet:
CaptainABC
  • 1,229
  • 6
  • 24
  • 40
  • But this assumes that the first row below the filtered range is empty. What if the first row below the filtered range is not blank? – Gary's Student Aug 29 '14 at 11:55
  • @Gary'sStudent I don't think so. I filtered a table and then ran the code, it gave me the correct first blank row, then I tried to put a letter in any column below the filtered range and every time I again got the correct result. – CaptainABC Aug 29 '14 at 11:59
  • So when I run this it returns the line below the last record, but not the line below the actual table. ie if our table had 1000 elemts, and filtered removed the last 200, then this returns 801 rather than 1001 – KOM Aug 29 '14 at 12:27
  • Sorry I mena below the filtered range – KOM Aug 29 '14 at 13:05
  • @SuperKOM If you are just looking for the row below the last row in the column then use only: `BlankRow = (Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row + 1)` instead all of the above. In this make sure the sheet name is correct and the you can change "A" to match the column you need. – CaptainABC Aug 29 '14 at 13:54