View Full Version : Solved: Unhide last row in auto filtered data
frank_m
02-06-2012, 04:25 AM
What I need to do is unhide the last row in the used range, even if it's hidden by auto filtering.
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
mohanvijay
02-06-2012, 04:44 AM
Try this
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
frank_m
02-06-2012, 06:00 AM
Hi mohanvijay (http://www.vbaexpress.com/forum/member.php?u=36127),
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
02-06-2012, 06:54 AM
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 (http://www.vbaexpress.com/forum/member.php?u=36127)
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.