VBA Formatting

O

OriginalStealth

I have a spreadsheet that has header rows and detail rows. The problem is
the header rows without details should only have 1 space below it and the
next header row should start. Sometimes the space is off between the rows.
This is also true with the detail rows. See example. How can I using VBA
delete the extra rows when sometimes it may be 2 extra rows, sometimes 3
extra rows or no extra rows.


HEADERROW1

HEADERROW2



HEADERROW3

DetailRow1
DetailRow2

HEADERROW4



DetailRow3
DetailRow4
 
D

Daniel.C

Try :

Sub test1()
Dim myRow As Long, i As Long
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
For i = myRow To 2 Step -1
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then
Rows(i - 1).Delete
ElseIf Cells(i, 2) <> "" Then
If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then
Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If
End Sub

Regards.
Daniel
 
O

OriginalStealth

Daniel, I got a "For without a next error on the code. I change it to below
and got a "Object Variable or With block variable not set" error. I'm
certain I screwed it up trying to fix it.

Sub test1()
Dim myRow As Long, i As Long


myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row

With myRow.Selection

For i = myRow To 2 Step -1
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then Rows(i -
1).Delete
ElseIf Cells(i, 2) <> "" Then

If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End With
'End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If


End Sub

Daniel.C said:
Try :

Sub test1()
Dim myRow As Long, i As Long
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
For i = myRow To 2 Step -1
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then
Rows(i - 1).Delete
ElseIf Cells(i, 2) <> "" Then
If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then
Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If
End Sub

Regards.
Daniel
I have a spreadsheet that has header rows and detail rows. The problem is
the header rows without details should only have 1 space below it and the
next header row should start. Sometimes the space is off between the rows.
This is also true with the detail rows. See example. How can I using VBA
delete the extra rows when sometimes it may be 2 extra rows, sometimes 3
extra rows or no extra rows.


HEADERROW1

HEADERROW2



HEADERROW3

DetailRow1
DetailRow2

HEADERROW4



DetailRow3
DetailRow4
Click to expand...
Click to expand...
 
D

Daniel.C

Ran my code again, no error.
In yours, I get en error on :
With myRow.Selection
which is incorrect.
Daniel
Daniel, I got a "For without a next error on the code. I change it to below
and got a "Object Variable or With block variable not set" error. I'm
certain I screwed it up trying to fix it.

Sub test1()
Dim myRow As Long, i As Long


myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row

With myRow.Selection

For i = myRow To 2 Step -1
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then Rows(i -
1).Delete
ElseIf Cells(i, 2) <> "" Then

If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End With
'End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If


End Sub

Daniel.C said:
Try :

Sub test1()
Dim myRow As Long, i As Long
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
For i = myRow To 2 Step -1
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then
Rows(i - 1).Delete
ElseIf Cells(i, 2) <> "" Then
If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then
Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If
End Sub

Regards.
Daniel
I have a spreadsheet that has header rows and detail rows. The problem is
the header rows without details should only have 1 space below it and the
next header row should start. Sometimes the space is off between the rows.
This is also true with the detail rows. See example. How can I using
VBA delete the extra rows when sometimes it may be 2 extra rows, sometimes
3 extra rows or no extra rows.


HEADERROW1

HEADERROW2



HEADERROW3

DetailRow1
DetailRow2

HEADERROW4



DetailRow3
DetailRow4
Click to expand...
Click to expand...
Click to expand...
 
O

OriginalStealth

Daniel, I grabbed your code and repasted it as is. I found that during the
paste It was breaking up one of the statements when I pasted it. I
corrected that. Now I am still getting an error. Here are my steps:

1. Copied your code as is
2. Opened excel, tools, Macro, VB Editor
3. Workbook, Insert Module, Paste Code
4. F8
5. When it gets to myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
I get the following error. Runtime Error 91. Object Variable or With Block
variable not set.
6. I even pasted the code in an empty workbook and still got the same error.

Daniel.C said:
Ran my code again, no error.
In yours, I get en error on :
With myRow.Selection
which is incorrect.
Daniel
Daniel, I got a "For without a next error on the code. I change it to below
and got a "Object Variable or With block variable not set" error. I'm
certain I screwed it up trying to fix it.

Sub test1()
Dim myRow As Long, i As Long


myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row

With myRow.Selection

For i = myRow To 2 Step -1
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then Rows(i -
1).Delete
ElseIf Cells(i, 2) <> "" Then

If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End With
'End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If


End Sub

Daniel.C said:
Try :

Sub test1()
Dim myRow As Long, i As Long
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
For i = myRow To 2 Step -1
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then
Rows(i - 1).Delete
ElseIf Cells(i, 2) <> "" Then
If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then
Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If
End Sub

Regards.
Daniel

I have a spreadsheet that has header rows and detail rows. The problem is
the header rows without details should only have 1 space below it and the
next header row should start. Sometimes the space is off between the rows.
This is also true with the detail rows. See example. How can I using
VBA delete the extra rows when sometimes it may be 2 extra rows, sometimes
3 extra rows or no extra rows.


HEADERROW1

HEADERROW2



HEADERROW3

DetailRow1
DetailRow2

HEADERROW4



DetailRow3
DetailRow4
Click to expand...
Click to expand...
Click to expand...
 
D

Daniel.C

Maybe your data looks different from mine. Here is the workbook I use
to test it :
http://www.filedropper.com/originalstealth
Daniel
Daniel, I grabbed your code and repasted it as is. I found that during the
paste It was breaking up one of the statements when I pasted it. I
corrected that. Now I am still getting an error. Here are my steps:

1. Copied your code as is
2. Opened excel, tools, Macro, VB Editor
3. Workbook, Insert Module, Paste Code
4. F8
5. When it gets to myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
I get the following error. Runtime Error 91. Object Variable or With Block
variable not set.
6. I even pasted the code in an empty workbook and still got the same error.

Daniel.C said:
Ran my code again, no error.
In yours, I get en error on :
With myRow.Selection
which is incorrect.
Daniel
Daniel, I got a "For without a next error on the code. I change it to
below and got a "Object Variable or With block variable not set" error.
I'm certain I screwed it up trying to fix it.

Sub test1()
Dim myRow As Long, i As Long


myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row

With myRow.Selection

For i = myRow To 2 Step -1
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then Rows(i -
1).Delete
ElseIf Cells(i, 2) <> "" Then

If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End With
'End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If


End Sub

:

Try :

Sub test1()
Dim myRow As Long, i As Long
myRow = [A:B].Find("*", [A1], , , , xlPrevious).Row
For i = myRow To 2 Step -1
If Cells(i, 1) <> "" Then
If Cells(i + 1, 2) <> "" Then Rows(i + 1).Insert
If Cells(i - 1, 1) = "" And Cells(i - 1, 2) = "" Then
Rows(i - 1).Delete
ElseIf Cells(i, 2) <> "" Then
If Cells(i - 1, 2) = "" And Cells(i - 2, 1) = "" Then
If Cells(i - 1, 1) = "" Then
Rows(i - 2).Delete
Else
Rows(i).Insert
End If
End If
End If
Next i
If Cells(2, 1) <> "" Or Cells(2, 2) <> "" Then
Rows(2).Insert
End If
End Sub

Regards.
Daniel

I have a spreadsheet that has header rows and detail rows. The problem
is the header rows without details should only have 1 space below it and
the next header row should start. Sometimes the space is off between
the rows. This is also true with the detail rows. See example. How
can I using VBA delete the extra rows when sometimes it may be 2 extra
rows, sometimes 3 extra rows or no extra rows.


HEADERROW1

HEADERROW2



HEADERROW3

DetailRow1
DetailRow2

HEADERROW4



DetailRow3
DetailRow4
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

deleted rows still show up in report 1
Help with VBA to find data delete it change font, add lines then looptill end of file 4
row comparison 5
automatically numbering rows 2
Creating Multiple Spreadsheets From One Using VBA 2
VBA Excel: Move rows which match a certain feature between tabs 3
weird loop problem- extra rows dissapearing 1
VBA MACRO Emergency!!!! 4

Top