Hi
I am new to VBA and I have the following problem:
I want to have different sections in a file and would like to start each section on a seperate page. I do not want to use a manual pagebreak since part of the page appears empty.
I would therefore like to insert blank rows untill the following section starts just after an automatic pagebreak. My code is as follows:
Sub insertrows()
Dim pg As Long
Dim T1 As Long
Dim T2 As Long
Dim x, y As Long
Dim rownum As Long
Dim i As Integer
Dim rowinsert As Integer
Dim SearchRange As Range
x = 1
With ActiveSheet
For pg = 1 To .HPageBreaks.Count
rownum = .HPageBreaks(pg).Location.Row
T1 = Application.Match(x, Range("A5:A1000"), 0)
If IsError(T1) Then
MsgBox "not found"
Else
MsgBox "found at pos: " & FindRowT1
End If
T2 = Application.Match(x + 1, ActiveSheet("A5:A1000"), 0)
If IsError(T2) Then
MsgBox "not found"
Else
MsgBox "found at pos: " & FindRowT2
End If
If T1 > rownum And T2 < rownum And x < 30 Then
x = x + 1
i = 0
ElseIf T1 < rownum And T2 < rownum And x < 30 Then
rowinsert = rownum - T2
For i = 1 To rowinsert
Rows(T2).Insert
Next i
x = x + 1
'T2 = T2 + 1
Else
x = x + 1
'T2 = T2 + 1
End If
Next
End With
End Sub
I have been facing the following problems:
T1 is able to be found, but T2 is giving the "Object does not support this property method".
I also tried with it with the following alternative
T1 = Application.WorksheetFunction.Match(x, Range("A1:A4000"), 0)
T2 = Application.WorksheetFunction.Match(x + 1, Range("A1:A4000"), 0)
but it is giving me an error on the second iteration.
Any suggestions is welcomed.
A sample file is also attached.
Niraj
Bookmarks