Loop through Excel Named Range with VBA

[Ben Jansen]

Hi All,
getting into the VBA programming and already managed to make my life a lot easier with some simple coding. This thing is cool to learn!
Now I am trying to level up and make my programming a bit more clean and professional by using NAMED RANGES.

My intention :
in my template I will have a named range with multiple rows and columns.
along the way I might add rows, so the amount of rows is not necessarily static.

I want VBA to loop through a certain Named Range and take action when a certain cell is or isn't filled out
(more specific, if it is not filled out, it should hide the row... Kind of a "clean-up my form code")

So in my head it is like :
'go to first line of named range
'check if column O and P is filled out
' - if it is filled out , go to the next range line
' - if it is not filled out, hide that row and go to the next line
'do this for the whole range

I am working with something similar to below now (VBA beginner code), but as soon as I insert some rows my code is screwed.
(And i have many many ranges programmed)

Any ideas would be appreciated !

Sub Button3_Click()

For a = 70 To 75
'Hide all Cells where nothing is filled out in column O and P
'under section Mech Pos Indication

    If Worksheets("Valve REPORT").Cells(a, 15).Value = "" And Worksheets("Valve REPORT").Cells(a, 16).Value = "" Then
    
    Worksheets("Valve REPORT").Rows(a).Hidden = True
    
    
    End If
End sub
    
    
Next

 

[Joe4]

Welcome to the Board!

Have a look at this tutorial here, which shows you how to loop through a named range:

Quite frankly, I don't really like working with dynamic named ranges, if I don't have to. They are susceptible to issues like:
1. If you add data below the last row in your data range, it will miss it (unless you have included some extra blank rows at the bottom of the named range.
2. They can get corrupted, especially if you are deleting rows/columns (I have seen a number of #REF errors in named ranges!)

I prefer to work with ranges dynamically, i.e. having Excel dynamically figure out exactly which ranges to include. For example, if I know that my data starts on row 2, and column A has an entry for every data row I want to include, I will find the last row in column with data, like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

and then loop through all my rows like this:

Dim r as Long
For r = 2 to lastRow
    ....
Next r

You can check specific columns within your loop like this (checking column E in this example):

If Cells(r, "E") = ...

Lastly, depending on what you are doing, sometimes you can avoid loops altogether. The general rule of thumb is to only use loops when absolutely necessary, as loop tend to be slow and are resource hogs. So if there are other ways to do something without using a loop, that is usually preferred.

Hope that helps!