nitrammada
Board Regular
- Joined
- Oct 10, 2018
- Messages
- 78
- Office Version
- 365
- Platform
- Windows
Hi Everyone,
I have some code that references each row with a page and row reference in column C starting at row 7, this code works fine. However, I have to filter out some unwanted rows not required for the report, so I make the unwanted row height = 0. Can someone help me to run the code only on the visible rows please. Below is the code that runs perfectly well, I figure I will have to wrap it in an If statement somehow, but I'm not sure about all the Dims and variables etc. Any help would be appreciated.
I have some code that references each row with a page and row reference in column C starting at row 7, this code works fine. However, I have to filter out some unwanted rows not required for the report, so I make the unwanted row height = 0. Can someone help me to run the code only on the visible rows please. Below is the code that runs perfectly well, I figure I will have to wrap it in an If statement somehow, but I'm not sure about all the Dims and variables etc. Any help would be appreciated.
VBA Code:
Sub RunPgNoSheet1()
'Adds page numbers for the Ref column C on Sheet1
Const c = 3, F1 = "=""", F2 = "/""&LEFT(ADDRESS(1,ROW()-", F3 = ",2),1+(ROW()>", F4 = "))"
r& = 7
With ActiveSheet
For L& = 1 To .HPageBreaks.Count
P& = r
r& = .HPageBreaks(L).Location.Row
.Columns(c).Rows(P & ":" & r - 1).Formula = F1 & L & F2 & P - 1 & F3 & P + 25 & F4
Next
P = r
r = .UsedRange.Rows.Count
If P <= r Then .Columns(c).Rows(P & ":" & r).Formula = F1 & L & F2 & P - 1 & F3 & P + 25 & F4
End With
End Sub