I have converted a PDF file to excel and in the conversion there is not a
standard number of lines between each entry. The record header is in column
U and I want to write a macro that will go down to the next non-blank entry
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.

--
Sara Jane

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1

Probbably easist to delete first all the blank rows then insert the 24 rows.

Sub DeleteEmptyRows()
''only if entire row is blank
lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
InsertRows22
End Sub

Sub InsertRows22()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "U").End(xlUp).Row
numRows = 23
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP


On Mon, 18 Jun 2007 18:05:10 GMT, "SaraJane via OfficeKB.com" <u34526@uwe
wrote:

I have converted a PDF file to excel and in the conversion there is not a
standard number of lines between each entry. The record header is in column
U and I want to write a macro that will go down to the next non-blank entry
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.

Thanks for the input - I can't delete the blank rows as I need the data in
columns A though T.

Gord Dibben wrote:
Probbably easist to delete first all the blank rows then insert the 24 rows.

Sub DeleteEmptyRows()
''only if entire row is blank
lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
InsertRows22
End Sub

Sub InsertRows22()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "U").End(xlUp).Row
numRows = 23
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

I have converted a PDF file to excel and in the conversion there is not a
standard number of lines between each entry. The record header is in column
U and I want to write a macro that will go down to the next non-blank entry
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.

--
Sara Jane

Message posted via http://www.officekb.com

Try this..........

Sub Delete_Blanks()
Dim rng As Range

Set rng1 = Columns("U")
rng1.SpecialCells(xlCellTypeBlanks).Delete

InsertCells

End Sub


Sub InsertCells()
Dim I As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For I = Cells(Rows.Count, "U").End(xlUp).Row To 2 Step -1
If Cells(I - 1, "U") < Cells(I, "U") Then _
Cells(I, "U").Resize(23, 1).Insert Shift:=xlDown
Next I
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord


On Mon, 18 Jun 2007 22:07:47 GMT, "SaraJane via OfficeKB.com" <u34526@uwe
wrote:

Thanks for the input - I can't delete the blank rows as I need the data in
columns A though T.

Gord Dibben wrote:
Probbably easist to delete first all the blank rows then insert the 24 rows.

Sub DeleteEmptyRows()
''only if entire row is blank
lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
InsertRows22
End Sub

Sub InsertRows22()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "U").End(xlUp).Row
numRows = 23
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

I have converted a PDF file to excel and in the conversion there is not a
standard number of lines between each entry. The record header is in column
U and I want to write a macro that will go down to the next non-blank entry
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.

Gord,
I put my cursor in cell U2 and tried the Delete_Blanks macro and nothing
happened. I stepped into the macro and got the following error message "run
time error1004 Application Defined or object defined error. I tried the
Insertcells macro and nothing happened. Again I stepped into the macro and
while went through it a couple of times and nothing happened.

I really appreciate your input - what am I doing wrong?

Gord Dibben wrote:
Try this..........

Sub Delete_Blanks()
Dim rng As Range

Set rng1 = Columns("U")
rng1.SpecialCells(xlCellTypeBlanks).Delete

InsertCells

End Sub

Sub InsertCells()
Dim I As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For I = Cells(Rows.Count, "U").End(xlUp).Row To 2 Step -1
If Cells(I - 1, "U") < Cells(I, "U") Then _
Cells(I, "U").Resize(23, 1).Insert Shift:=xlDown
Next I
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord

Thanks for the input - I can't delete the blank rows as I need the data in
columns A though T.
[quoted text clipped - 33 lines]
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.

--
Sara Jane

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1

My mistake............left out the Shift:= xlUp

Use this macro for deleting blank cells then run the InsertCells macro.

You don't have to select any cell in column U

Sub Delete_Blanks()
Dim rng As Range

Set rng1 = Columns("U")
rng1.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

' InsertCells

End Sub


Gord

On Tue, 19 Jun 2007 11:33:05 GMT, "SaraJane via OfficeKB.com" <u34526@uwe
wrote:

Gord,
I put my cursor in cell U2 and tried the Delete_Blanks macro and nothing
happened. I stepped into the macro and got the following error message "run
time error1004 Application Defined or object defined error. I tried the
Insertcells macro and nothing happened. Again I stepped into the macro and
while went through it a couple of times and nothing happened.

I really appreciate your input - what am I doing wrong?

Gord Dibben wrote:
Try this..........

Sub Delete_Blanks()
Dim rng As Range

Set rng1 = Columns("U")
rng1.SpecialCells(xlCellTypeBlanks).Delete

InsertCells

End Sub

Sub InsertCells()
Dim I As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For I = Cells(Rows.Count, "U").End(xlUp).Row To 2 Step -1
If Cells(I - 1, "U") < Cells(I, "U") Then _
Cells(I, "U").Resize(23, 1).Insert Shift:=xlDown
Next I
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord

Thanks for the input - I can't delete the blank rows as I need the data in
columns A though T.
[quoted text clipped - 33 lines]
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.