Solved: Unhide last row in auto filtered data

[frank_m]

What I need to do is unhide the last row in the used range, even if it's hidden by auto filtering.[vba]
Sub Unhide_LastRow()

Dim LastRow As Long

With ActiveSheet

'this is finding the last visible row, when I need the actual last row.
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row

'Rows(LastRow & ":" & LastRow).EntireRow.Hidden = False
.Cells(LastRow, 7).EntireRow.Hidden = False
.Cells(LastRow, 7).RowHeight = 16.5

End With

End Sub
[/vba]

[mohanvijay]

Try this

[vba]
Dim H_Row As Variant
Dim L_Row As Long
With ActiveSheet
H_Row = Split(.UsedRange.Address, "$")
L_Row = CLng(H_Row(UBound(H_Row)))

.Range("a" & L_Row).EntireRow.Hidden = False
End With
[/vba]

[frank_m]

Hi mohanvijay,

Your code works, thanks. - I'm wondering though if it would be much trouble for you to modify it to use the last row in Column G, rather than the used range, because some times the user has cleared the contents of a row, rather than deleting it, causing the last row of the used range to be an empty row below the last row containing data.

Thanks

[frank_m]

Using google I found the code below at the ExcelForum. It seems I'm now in good shape.

Edit: Tested this more and found that it does work even when the used range does not match the last row with data... Issue solved

I really appreciate your help and your time mohanvijay
[vba]
Sub Unhide_LastRow()
'http://www.excelforum.com/excel-programming/708977-find-last-row-including-hidden-and-filtered-rows.html
Dim L_Row As Long
L_Row = LastUsedRow(ActiveSheet) - 1

ActiveSheet.Range("G" & L_Row).EntireRow.Hidden = False

End Sub

Function LastUsedRow(Optional wks As Worksheet) As Long
' Visible or hidden, filtered or not
Dim iFilt As Long
Dim iFind As Long

With IIf(wks Is Nothing, ActiveSheet, wks)
If WorksheetFunction.CountA(.UsedRange) = 0 Then Exit Function
If .FilterMode Then iFilt = .AutoFilter.Range.Row + .AutoFilter.Range.Rows.Count - 1
iFind = .Columns(7).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
End With
LastUsedRow = IIf(iFind > iFilt, iFind, iFilt)
End Function
[/vba]