0

I can get the VBA to work just fine to do this, but my spreadsheet is 20MB without data. What I want to do is find the first time a cell is blank and then hide every row at once from that spot until the end. Here is my code that works:

Dim rl As Range
For Each rl In Sheets("Results").Range("$A$4:$A$800")
  If rl.Value = "" Then
      r1.EntireRow.Hidden = True
  Else: rl.EntireRow.Hidden = False
  End If
Next rl

So in this example, if r1.value = "", I want to hide from that row all the way until row 800. Any advice?

  • 1
    An excel sheet of 20 mb usually is the result of an export loaded and misunderstood by excel, because all columns even when blank and all rows even when blank are present in the sheet. If you go to the last row with data and make a selection all the way to the end and delete those rows, and you do the same for all columns, chances are that you'll get a 200kb or less excell sheet. This action can be done by hand within a minute if you use the shift key to make your selection. Click on the first row of your selection, scroll down using the scroll bar, hold shift and click the last row. – LPChip Jul 24 '15 at 20:33
  • also, make sure you save the sheet after the deletion, then close and reload, otherwise you won't see the effect. – LPChip Jul 24 '15 at 20:33
  • The size of the spreadsheet is due to the extensive formulas within it, but because of the shear size, the macro above takes a long time to perform, and I would like to make it more efficient. – Nicholas Conklin Jul 24 '15 at 20:38
  • @NicholasConklin, I'm not sure you're going to get much... You could try to disable screen updates but I feel you've answered your question. The issue is the extensive formulas, you're going to have this issue what ever you do IMO – Dave Jul 27 '15 at 14:14
  • This has nothing to do with the size of my file, other than, to be more efficient, once I hit the first cell that is blank, hide all rows from that row until row 800. I am sure there is an easy way to do it, just my syntax is wrong when I try. – Nicholas Conklin Jul 27 '15 at 19:41
0

For making it faster try this:

Sub Test()
  Dim i As Long
  For i = 4 To 800
    If Sheets("Results").Cells(i, 1).Value = "" Then
      Rows(i & ":" & Rows.Count).EntireRow.Hidden = True
      Rows("1:" & i - 1).EntireRow.Hidden = False
      Exit Sub
    End If
  Next
End Sub

it checks for the first cell in row A without text and hides all rows below it (includes the cell found) and makes all cells visible over it (excluding the found cell)
this will also accept cells as "no text" with formulas having a "" as result

for only hide starting with the real empty cell change

 If Sheets("Results").Cells(i, 1).Value = "" Then

to

If isempty(Sheets("Results").Cells(i, 1).Value) Then

also when only hiding till row 800 change

Rows(i & ":" & Rows.Count).EntireRow.Hidden = True

to

Rows(i & ":800").EntireRow.Hidden = True
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.