24 lines between data

[SaraJane via OfficeKB.com]

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 Dibben]

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

 

[SaraJane via OfficeKB.com]

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

 

[Gord Dibben]

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

 

[SaraJane via OfficeKB.com]

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?

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]

 

[Gord Dibben]

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

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?

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.

 

[Gord Dibben]

Just as an alternate method of deleting blanks.

Select column U and F5>Special>Blanks>OK

Edit>Delete>Shift cells up.


Gord

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

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?

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.

 

[SaraJane via OfficeKB.com]

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)

Just as an alternate method of deleting blanks.

Select column U and F5>Special>Blanks>OK

Edit>Delete>Shift cells up.

Gord

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

[quoted text clipped - 58 lines]

 

[Gord Dibben]

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
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)

Just as an alternate method of deleting blanks.

Select column U and F5>Special>Blanks>OK

Edit>Delete>Shift 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.

 

[SaraJane via OfficeKB.com]

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

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]

 

[Gord Dibben]

My posting email addess is gorddibbATshawDOTca

Change the AT and DOT to punctuation


Gord

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

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.