CODEWITHSUNDEEP
vbaexcel
Kim
Kim

Reputation: 163

Making Cells Blank for Hidden Rows

I'm brand new to VBA and am trying to optimize a spreadsheet for work.

Ultimately, I'm trying to write a script that looks for hidden rows. If a row is hidden, then the cell in column "O" in that row should be blank. Here's what I have (that is not working and may be completely wrong):

Dim rng As Range
Dim c As Range

Set rng = Range("A1:A1500")
For Each c In rng
    If c.EntireRow.Hidden = True Then
        ActiveCell.Offset(0, 14).Activate
        ActiveCell.Value = ""
    End If
Next c

Thank you for your help! Kim

Upvotes: 2

Views: 97

Answers (3)

Dirk Reichel
Dirk Reichel

Reputation: 7979

If there are no formulas and you want a non-loop-solution, you simply can run this 1-liner:

Range("O1:O1500") = Evaluate("INDEX(IF(SUBTOTAL(103,OFFSET(O1,ROW(O1:O1500)-1,)),O1:O1500,""""),)")

The OFFSET will just return a lot of #VALUE! which still have a single-cell-ref. This is the key for having SUBTOTAL to output an array (instead of a single value) which with option 3 just returns 1 for all items inside the array while having option + 100 will exclude hidden rows. Using this inside the IF will simply be true for visible rows and false for hidden ones. Now true returns the cells value while false only returns a simple empty string. The INDEX is just for forcing the Evaluate to return an array which then simply is set for the range. Everything in one calculation without any loop :D

Upvotes: 0

braX
braX

Reputation: 11755

Update: I just tested this and it works:

  Dim rng As Range
  Dim c As Range

  Set rng = Range("A1:A1500")
  For Each c In rng
    If c.EntireRow.Hidden = True Then
      Range("O" & c.Row).Value = ""
    End If
  Next

Upvotes: 1

Liss
Liss

Reputation: 441

I always prefer to loop through with long integers and avoid ActiveCells. Turn off screen updating as well if it's going to be long.

Dim c As Long

For c = 1 To 1500
    If Rows(c).Hidden = True Then
        Cells(c, "O") = ""
    End If
Next c

Upvotes: 2

Related Questions