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

 

[johnnyL]

The Desired result should be the following pic ...

No blanks in column 4 aka the D column.

 

[Shadkng]

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

 

[Shadkng]

the cells in column D will not have any formulas or data, only what I input into the cell. As far as the number of lines, I used 700 just to cover myself, but I guess it would be better not to have to worry about revising the code if I exceeded that at some point. I see there was a comment about starting from the bottom and working up? Right now I am using the code from post #2 which is working well. Thanks

 

[Rick Rothstein]

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

When did the OP show us that original file? Also, when did the OP say there were duplicates values that had to be "collapsed" into a single cell with counts showing?

 

[Shadkng]

When did the OP show us that original file? Also, when did the OP say there were duplicates values that had to be "collapsed" into a single cell with counts showing?

I never provided any file...I think one of the experts did as an example.

 

[johnnyL]

When did the OP show us that original file? Also, when did the OP say there were duplicates values that had to be "collapsed" into a single cell with counts showing?

my apologies @Rick Rothstein for any confusion I have caused. The OP didn't provide a file, The pics I provided were the results of a previous file I had worked on, the alleged 'original' file, and was applying the code from this thread. It was merely to demonstrate the results to column 4 and the C column. Again, my apologies for any confusion.

 

[Shadkng]

Is below the best way to un-hide the rows and columns? Thanks

Sub UNHIDE_ROWS()
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
End Sub

 

[Rick Rothstein]

Not sure if it is better, but it is shorter...

Rows.Hidden = False
Columns.Hidden = False

 

[Shadkng]

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

Hi Rick,
it just so happens that I needed to use my code in another program to hide rows that were NOT truly blank and had formulas in them. So I used your above line which worked fine - so thanks for thinking of that possibility.