24 lines between data
 

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

24 lines between data
 

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.

24 lines between data
 

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

24 lines between data
 

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.

24 lines between data
 

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

24 lines between data
 

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.

24 lines between data
 

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.

24 lines between data
 

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

24 lines between data
 

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.

24 lines between data
 

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

24 lines between data
 

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.