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.

Just as an alternate method of deleting blanks.

Select column U and F5SpecialBlanksOK

EditDeleteShift cells up.


Gord

On Tue, 19 Jun 2007 08:25:53 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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.

Thanks again for your input - I think I have done a poor job describing what
I need. I converted a PDF file to excel and there are hundreds of records
with lots of corresponding data. The data ranges from column A through
columns U. There are anywhere from 10 rows to 23 rows in each record. I want
to have each record span 24 rows. So for those records with only 10 rows I
want to add 14 rows at the bottom of the record (i.e.Record X ends on row 10
I want to insert 14 rows so that it will end on the standard of 24th rows or
row 34 and have data in columns A through U)

Gord Dibben wrote:
Just as an alternate method of deleting blanks.

Select column U and F5SpecialBlanksOK

EditDeleteShift cells up.

Gord

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

[quoted text clipped - 58 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 http://www.officekb.com

Definitely not what your original post asked for.

email me the workbook if you wish.

Change the AT and DOT to get my address.

I'll have a look at the layout and see what can be done.


Gord

On Tue, 19 Jun 2007 17:08:10 GMT, "SaraJane via OfficeKB.com" <u34526@uwe
wrote:

Thanks again for your input - I think I have done a poor job describing what
I need. I converted a PDF file to excel and there are hundreds of records
with lots of corresponding data. The data ranges from column A through
columns U. There are anywhere from 10 rows to 23 rows in each record. I want
to have each record span 24 rows. So for those records with only 10 rows I
want to add 14 rows at the bottom of the record (i.e.Record X ends on row 10
I want to insert 14 rows so that it will end on the standard of 24th rows or
row 34 and have data in columns A through U)

Gord Dibben wrote:
Just as an alternate method of deleting blanks.

Select column U and F5SpecialBlanksOK

EditDeleteShift cells up.

Gord

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

[quoted text clipped - 58 lines]
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.

I'm sorry - I don't know what you mean when you say change the AT and DOT to
get your address

Gord Dibben wrote:
Definitely not what your original post asked for.

email me the workbook if you wish.

Change the AT and DOT to get my address.

I'll have a look at the layout and see what can be done.

Gord

Thanks again for your input - I think I have done a poor job describing what
I need. I converted a PDF file to excel and there are hundreds of records
[quoted text clipped - 18 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 posting email addess is gorddibbATshawDOTca

Change the AT and DOT to punctuation


Gord

On Tue, 19 Jun 2007 18:41:14 GMT, "SaraJane via OfficeKB.com" <u34526@uwe
wrote:

I'm sorry - I don't know what you mean when you say change the AT and DOT to
get your address

Gord Dibben wrote:
Definitely not what your original post asked for.

email me the workbook if you wish.

Change the AT and DOT to get my address.

I'll have a look at the layout and see what can be done.

Gord

Thanks again for your input - I think I have done a poor job describing what
I need. I converted a PDF file to excel and there are hundreds of records
[quoted text clipped - 18 lines]
in column U and have there be 24 lines between entries. I need to insert the
lines just above the next entry.