VBA hide rows takes long

[Shadkng]

Hi, I was wondering if the code below can be improved to be more efficient. It takes a long time and sometimes locks up the file. Thanks

Sub HIDE_ROWS()
Application.ScreenUpdating = False
    BeginRow = 5
    EndRow = 700
    ChkCol = 4

    For RowCnt = BeginRow To EndRow
            If Cells(RowCnt, ChkCol).Value = "" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    Range("c:c").EntireColumn.Hidden = True
    Application.ScreenUpdating = True
End Sub

 

[6StringJazzer]

Try this. I have not set up a file to replicate yours, but generally pushing the work to VBA will result in better efficiency. Also you should use Option Explicit and declare variables.

The code you showed should never lock up the file. There may be other issues not evident from your code.

Sub HIDE_ROWS()

    Application.ScreenUpdating = False

    Const BeginRow = 5
    Const EndRow = 700
    ChkCol = 4

    Range(Cells(BeginRow,ChkCol),Cells(EndRow,ChkCol)).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

    Range("c:c").EntireColumn.Hidden = True


    Application.ScreenUpdating = True
End Sub

 

[Shadkng]

Try this. I have not set up a file to replicate yours, but generally pushing the work to VBA will result in better efficiency. Also you should use Option Explicit and declare variables.

The code you showed should never lock up the file. There may be other issues not evident from your code.

Sub HIDE_ROWS()

    Application.ScreenUpdating = False

    Const BeginRow = 5
    Const EndRow = 700
    ChkCol = 4

    Range(Cells(BeginRow,ChkCol),Cells(EndRow,ChkCol)).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

    Range("c:c").EntireColumn.Hidden = True


    Application.ScreenUpdating = True
End Sub

Hi,
Much better. What do you mean by "pushing the work to VBA?" I am a novice so not familiar with some of this. Thanks

 

[6StringJazzer]

You wrote a loop to go through every row, and inspect every cell in that column. VBA has a built-in way to do it, SpecialCells. There are many other VBA features that allow you to do things to an entire range at once, rather than looping through every cell in that range. The code for SpecialCells is compiled object code, whereas the code you write is interpreted code, so anything you can let built-in features do for you will be screaming fast compared to writing your own VBA to do it.

 

[Michael M]

Are the rows truly blank or do they contain a formula that returns a blank.
Also when hiding or deleting tows, you should work up from the bottom of your data
Maybe this way

Sub MM1()
  On Error Resume Next
  Range("D1", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).EntireRow.Hidden = True
End Sub

 

[johnnyL]

Are the rows truly blank or do they contain a formula that returns a blank.
Also when hiding or deleting tows, you should work up from the bottom of your data
Maybe this way

Sub MM1()
  On Error Resume Next
  Range("D1", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).EntireRow.Hidden = True
End Sub

Some good points raised there. If a 4th column cell is not truly empty then this will not work as desired. As far as the approach to work from the bottom up, starting at the very bottom of the Excel file may not be desired, The OP stated that the end row should be 700, if the excel file contains more rows than that, undesired results may occur.

All that being said I propose we start with the following code and take it from there if further info is made available:

Sub HIDE_ROWS()
'   Objective:
'       Hide all Rows that have no information whatsoever in the 4th column & Hide Column C regardless
'
    Application.ScreenUpdating = False          ' Turn off ScreenUpdating to speed up processing
'
    Const BeginRow = 5
    Const EndRow = 700
    ChkCol = 4
'
    On Error Resume Next                        ' Turn on Manual Error Handling, If error encountered, proceed to next line of code
'
'   If column 4, aka Column D, in a row between a certain range is absent of any formulas, data, etc ... aka truly blank, then hide the row
    Range(Cells(EndRow, ChkCol), Cells(BeginRow, ChkCol)).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
'
    Range("c:c").EntireColumn.Hidden = True     ' Hide Column C
'
    On Error GoTo 0                             ' Turn off Manual Error Handling

    Application.ScreenUpdating = True           ' Turn ScreenUpdating back on
End Sub

 

[johnnyL]

Ok, here goes another attempt to handle the Cell appears to be empty, but a formula in that cell resulted in an apparent blank cell scenario...

Sub HIDE_ROWS()
'   Objective:
'       Hide all Rows that have no information whatsoever in the 4th column  & Hide Column C regardless
'
    Application.ScreenUpdating = False          ' Turn off ScreenUpdating to speed up processing
'
    Const BeginRow = 5
    Const EndRow = 700
    ChkCol = 4
'
    On Error Resume Next                        ' Turn on Manual Error Handling, If error encountered, proceed to next line of code
'
'   If column 4, aka Column D, in a row between a certain range is absent of any formulas, data, etc ... ie. truly blank, then hide the row
    Range(Cells(EndRow, ChkCol), Cells(BeginRow, ChkCol)).AutoFilter 1, "<>", , , False
'
    Range("c:c").EntireColumn.Hidden = True     ' Hide Column C
'
    On Error GoTo 0                             ' Turn off Manual Error Handling

    Application.ScreenUpdating = True           ' Turn ScreenUpdating back on
End Sub

 

[Rick Rothstein]

Ok, here goes another attempt to handle the Cell appears to be empty, but a formula in that cell resulted in an apparent blank cell scenario...

If the cells in the ChkCol columns contain formulas that return numbers (dates included) or the empty text string (""), then we can still use SpecialCells to find the "blanks"...

Range(Cells(BeginRow,ChkCol),Cells(EndRow,ChkCol)).SpecialCells(xlFormulas, xlTextValues).EntireRow.Hidden = True

 

[johnnyL]

@Rick Rothstein That doesn't yield the same results In this scenario

 

[johnnyL]

I enclosed a photo to show the original file and a photo to show the results from your suggestion.