CODEWITHSUNDEEP
excelvba
Cathy11
Cathy11

Reputation: 35

VBA loop to hide particular values in a column

I have a column in a table and want to hide the rows in it that have a particular string “work1.”

Fyi it's an interactive table so can have different filters applied at a time.

I'm unsure what to change in the vba code to achieve this…

Here is the section of code:

Dim c As Range

For Each c In Range (“T:T). Cells
    If c.Value = “Set1” Then
        c.EntireRow.Hidden = True
    End If 
Next c

Upvotes: 0

Views: 55

Answers (1)

Jeremy Thompson
Jeremy Thompson

Reputation: 65554

A couple of things:

  1. You don't want to iterate through the entire T column, in Excel > 2003 its over a million rows! Calculate the Max Rows in a long and only iterate over the used cells.

  2. When you run this macro twice, without the sheet.Rows.Hidden = False then it won't work because the cells are already hidden. That's the problem with the filtering...

Sub Macro1()
Dim c As Range
Dim inputRange As Range
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
sheet.Rows.Hidden = False

Dim lngMaxRow As Long
lngMaxRow = Range("T" & Rows.Count).End(xlUp).Row
Set inputRange = Range("T1:T" & lngMaxRow)
    
For Each c In inputRange
    If Len(c.Value) > 0 Then
        c.EntireRow.Hidden = True
    End If
Next c
End Sub

Upvotes: 1

Related Questions