Hi,
Can anyone help me to filter and hide rows if Column AS to AV has zero values until last row.
Regards
Hi,
Can anyone help me to filter and hide rows if Column AS to AV has zero values until last row.
Regards
Hi,
I'd use a helper column that summed the AS:AV values and then used that column to filter for non zeros.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
Try
PHP Code:
Sub HideRows()
Dim i As Integer
Application.ScreenUpdating = 0
For i = 6 To 400
If Sheets(1).Cells(i, 45).Value = 0 And Sheets(1).Cells(i, 46).Value = 0 And Sheets(1).Cells(i, 47).Value = 0 And Sheets(1).Cells(i, 48).Value = 0 Then
Worksheets("Sheet1").Rows(i).Hidden = True
End If
Next
Application.ScreenUpdating = 1
End Sub
Last edited by jeffreybrown; 04-08-2017 at 09:08 AM.
Thanks, the works perfectly. The rows will not be fixed always, there is huge data in my original file. Can I request you to set range for the last used row.
Regards,
Change:
PHP Code:
Sub HideRows2()
Dim lr As Long, i As Long
lr = Range("A65000").End(3).Row
Application.ScreenUpdating = 0
For i = 6 To lr
If Sheets(1).Cells(i, 45).Value = 0 And Sheets(1).Cells(i, 46).Value = 0 And Sheets(1).Cells(i, 47).Value = 0 And Sheets(1).Cells(i, 48).Value = 0 Then
Sheets(1).Rows(i).Hidden = True
End If
Next
Application.ScreenUpdating = 1
End Sub
... a word of caution
If there is indeed a huge number of rows in your data, looping through cells with a macro will take a long time. That's because there is a time overhead each time VBA jumps back to Excel to evaluate and take action and then returns to VBA
The fastest and most efficient way I know of doing this sort of stuff is to filter the data for the stuff you want to hide and then use a single line of 'your_range.SpecialCells(xlCellTypeVisible).Hidden = True
Thanks, Solved
Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks