VBA Formatting

  • Thread starter Thread starter OriginalStealth
  • Start date Start date
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
 
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top