delete blank rows automatically between rows of data

[bhattjai]

I have data that is arranged down column A with the following format:
--------------------
<TABLE style="WIDTH: 223pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=297 border=0 x:str><COLGROUP><COL style="WIDTH: 223pt; mso-width-source: userset; mso-width-alt: 10861" width=297><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 223pt; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=297 height=17>Last Name, First Name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=17>Address</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=17>City, State Zip</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=17>County

There is a few spaces between the County and the next record (the amount of spaces is always different). I have the following macro to copy and transpose the data and then delete the empty spaces between the rows to get the data in a nice order:

Sub JudgmentMacro1()
Dim myAreas As Areas, myArea As Range
On Error Resume Next
Set myAreas = Columns(1).SpecialCells(2).Areas
If myAreas Is Nothing Then Exit Sub
For Each myArea In myAreas
For Each r In myArea.Resize(, 2)
If r.Value <> "" Then
n = n + 1
myArea(1).Offset(, n + 2).Value = r.Value
End If
Next
n = 0
Next
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=1
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("D:G").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Columns("E:G").Select
ActiveWindow.SmallScroll ToRight:=1
Selection.Cut Destination:=Columns("F:H")
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("E:E").EntireColumn.AutoFit
End Sub

My problem is this: When I delete the empty rows, there are some records that have data missing: example:

Last Name, First Name
Address
County
(NOTICE: THE LINE WITH CITY, STATE, ZIP is missing). So this throws off the entire data when it is sorted with the macro above. Any way to have the macro skip over the data if its is NOT 4 rows in length??? OR, is there a way to delete rows without using the method I use above of deleting ALL blank cells. Basically, I want this data to be ready for mail merge, and if there are rows of blank cells in between, it will not be useful for mail merge with a word document. (Note: I am a macro idiot : )

Thank you in advance.</TD></TR></TBODY></TABLE>

 

[jindon]

Why don't you continue the original thread?
Leaving unresponded your original thread is your habit?