Select Area in spreadsheet which changes

**vietafghantx** · 11-04-2014, 09:18 PM

I have a spreadsheet (attached) which will sort columns based on different criteria. Once I sort out the columns that I do not need, I would like to then sort and hide rows under the remaining columns which do not have data in that row.

I have this code below which will hide rows which do not have data in them but I must put in the correct area to search. (Set r = Range ("F16:AC1750")

What I would like to have is code which will find the visible remaining columns and put this information into the r= range () to sort/hide the rows.

Thank you

Private Sub Hide_Rows()

Worksheets("BOQMasterList").Activate
Dim r As Range, c As Range
Set r = Range("F16:AC1750")
Application.ScreenUpdating = False
For Each c In r.Rows
If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
End Sub

**Kaper** · 11-05-2014, 06:44 AM

As a starting point try:

Please Login or Register  to view this content.

I probably not followed the part about columns (as a matter of fact did not understood it well - which columns used and where length of cells shall be tested) so based on columns F and G as your data range and cells 1,2 suggested).

PS. Note, how readability improves after applying code tags to the code. And please do edit your post (code tags are required by our http://www.excelforum.com/forum-rule...rum-rules.html )