CODEWITHSUNDEEP
excelvba
D. Nijland
D. Nijland

Reputation: 13

How to make Excel VBA adjust the height of every n th row using a for loop?

I can't make Excel VBA adjust the row height depending on calculated row numbers. I calculate which rows need to be adjusted in height, using a for loop. Then I want to "select" those rows to make Excel adjust their height. (to ensure that they become hidden) However, the error "Application-defined or object-defined error" keeps coming back.

I tried several options where change the way the rows are defined. Using "with workbook" and stuff like that. I feel that only a minor change is required to the code I have, therefore I just provide this one.

Sub ChangeRowHeight()
Dim FindRowNumber As Long
Dim FindRow As Range
Dim i As Long
Dim n As Long
Dim m As Long

    Set FindRow = Range("A:A").Find(What:="Total Forecast 2022", LookIn:=xlValues)
    k = FindRow.Row


For i = 1 To (k - 9) / 9
        n = ((9 * i) + 1)
        m = ((9 * i) + 8)
        Rows("n:m").RowHeight = 1

    Next i

End Sub

I would like the VBA to first count the amount of Rows up to a particular item. (that works fine) And then determine n-times 8 rows in between that need to be adjusted in height (or hidden).

Upvotes: 0

Views: 1180

Answers (3)

D. Nijland
D. Nijland

Reputation: 13

For anyone interested in a similar code. The final code became the following. I made a macro-button to hide/unhide every nth row (as described above). I had to avoid that the first few rows hide as well, so the loop after a few rows. Also, the macro ran too far (because I adjusted it), therefore, I substract 2 from the rows, to avoid this issue.

Sub Change_Row_Height()
Dim FindRow As Range
Dim iRow As Long

Set FindRow = Range("A:A").Find(What:="Total Forecast 2022", LookIn:=xlValues, LookAt:=xlWhole)

If FindRow Is Nothing Then
    MsgBox "'Total Forecast 2022' was not found!", vbCritical
    Exit Sub
End If

Dim LastRow As Long
LastRow = FindRow.Row

If Rows(10).Hidden = False Then
For iRow = 9 To LastRow - 2 Step 9
    Rows(iRow + 1 & ":" & iRow + 7).Hidden = True
Next iRow
ElseIf Rows(10).Hidden = True Then
For iRow = 9 To LastRow - 2 Step 9
    Rows(iRow + 1 & ":" & iRow + 7).Hidden = False
Next iRow
End If

End Sub

Upvotes: 0

Avin
Avin

Reputation: 1

lastrow = ws.usedrange.rows.count

ws.range("A1:A"&lastrow).rows.autofit

Upvotes: -1

Pᴇʜ
Pᴇʜ

Reputation: 57743

This code

Dim i As Long
For i = 1 To 100 Step 9
    Debug.Print i
Next i

will output

 1 
 10 
 19 
 28 
 37 
 46 
 55 
 64 
 73 
 82 
 91 
 100

So you can use Rows(i) to get every 9ᵗʰ row.

The rows between every 9ᵗʰ row can be accessed with

Debug.Print i + 1 & ":" & i + 8

Which would be 

2:9
11:18
20:27
29:36
38:45
47:54
56:63
65:72
74:81
83:90
92:99
101:108

Note that if using Find you need always to specify the LookAt as xlWhole or xlPart otherwise Excel uses what ever was used before by the user (and you never know which one it is) because there is no default.

Dim FindRow As Range
Set FindRow = Range("A:A").Find(What:="Total Forecast 2022", LookIn:=xlValues LookAt:=xlWhole)

Also you need to test if something was found after you used find()or you will run into an error:

If FindRow Is Nothing Then
    MsgBox "'Total Forecast 2022' was not found!", vbCritical
    Exit Sub
End If

Dim LastRow As Long
LastRow = FindRow.Row

Dim iRow As Long
For iRow = 1 To LastRow Step 9
    Rows(iRow + 1 & ":" & iRow + 8).Hidden = True
Next iRow

Upvotes: 2

Related Questions