simple macro?
 

I need a macro that will from A1 test if B1 is blank and if A2<"X" delete
row A1, else move down and repeat.

I have a col of customer info in Col B which ahs extra blank rows within.
An "X" in Col A denotes a new customer so the last line of a customer info
should be blank hence the test above for A2

I was a wizz at Lotus macros but new to Excel VBA.

simple macro?
 

This does what you described, but I need to point out that it is based on
the assumption that each customer record consists of two lines of data and
that you only want to delete the first line if it meet the conditions that
you described. If that is not what you want, then re-define your problem
and re-post.

Also, this works from the bottom up, which prevents skipping any consecutive
bland rows.

Sub chkforblank()
Dim lr As Long, i As Long, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
If lr Mod 2 = 0 Then
For i = lr To 1 Step -2
If sh.Cells(i - 1, 2) = "" And _
sh.Cells(i, 1) < "X" Then
Rows(i - 1).Delete
End If
Next
End If
End Sub



"MusicMan" wrote in message
...
I need a macro that will from A1 test if B1 is blank and if A2<"X" delete
row A1, else move down and repeat.

I have a col of customer info in Col B which ahs extra blank rows within.
An "X" in Col A denotes a new customer so the last line of a customer info
should be blank hence the test above for A2

I was a wizz at Lotus macros but new to Excel VBA.

simple macro?
 

The number of lines per customer varies from 2 to 8. I'll look at this and
see if it can be adapted. The problem is with the extra embedded blank lines
other than one at the end of each record.

"JLGWhiz" wrote:

This does what you described, but I need to point out that it is based on
the assumption that each customer record consists of two lines of data and
that you only want to delete the first line if it meet the conditions that
you described. If that is not what you want, then re-define your problem
and re-post.

Also, this works from the bottom up, which prevents skipping any consecutive
bland rows.

Sub chkforblank()
Dim lr As Long, i As Long, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
If lr Mod 2 = 0 Then
For i = lr To 1 Step -2
If sh.Cells(i - 1, 2) = "" And _
sh.Cells(i, 1) < "X" Then
Rows(i - 1).Delete
End If
Next
End If
End Sub



"MusicMan" wrote in message
...
I need a macro that will from A1 test if B1 is blank and if A2<"X" delete
row A1, else move down and repeat.

I have a col of customer info in Col B which ahs extra blank rows within.
An "X" in Col A denotes a new customer so the last line of a customer info
should be blank hence the test above for A2

I was a wizz at Lotus macros but new to Excel VBA.

simple macro?
 

It will be difficult to put it into a loop for deleting the rows unless
there is some factor in each variable length record that can be used to
identify the beginning and ending line. Or at least the beginning line of
each record if that is the one you want to delete. Computers are not that
smart, they have to be told what to look for. If you can think of some
consistent piece of data that can be used , re-post and we will give it
another stab.


"MusicMan" wrote in message
...
The number of lines per customer varies from 2 to 8. I'll look at this
and
see if it can be adapted. The problem is with the extra embedded blank
lines
other than one at the end of each record.

"JLGWhiz" wrote:

This does what you described, but I need to point out that it is based on
the assumption that each customer record consists of two lines of data
and
that you only want to delete the first line if it meet the conditions
that
you described. If that is not what you want, then re-define your problem
and re-post.

Also, this works from the bottom up, which prevents skipping any
consecutive
bland rows.

Sub chkforblank()
Dim lr As Long, i As Long, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
If lr Mod 2 = 0 Then
For i = lr To 1 Step -2
If sh.Cells(i - 1, 2) = "" And _
sh.Cells(i, 1) < "X" Then
Rows(i - 1).Delete
End If
Next
End If
End Sub



"MusicMan" wrote in message
...
I need a macro that will from A1 test if B1 is blank and if A2<"X"
delete
row A1, else move down and repeat.

I have a col of customer info in Col B which ahs extra blank rows
within.
An "X" in Col A denotes a new customer so the last line of a customer
info
should be blank hence the test above for A2

I was a wizz at Lotus macros but new to Excel VBA.

simple macro?
 

Restated. Delete line if col B is blank unless next row of a ="X"
Delete all excess blank lines
A B
1 X Name1
2 Add1
3 delete this and any other blanks lines
4 Add2
5 city
6 except this one as the next line is the next
cust with "X" in A
7 X Name2

"JLGWhiz" wrote:

It will be difficult to put it into a loop for deleting the rows unless
there is some factor in each variable length record that can be used to
identify the beginning and ending line. Or at least the beginning line of
each record if that is the one you want to delete. Computers are not that
smart, they have to be told what to look for. If you can think of some
consistent piece of data that can be used , re-post and we will give it
another stab.


"MusicMan" wrote in message
...
The number of lines per customer varies from 2 to 8. I'll look at this
and
see if it can be adapted. The problem is with the extra embedded blank
lines
other than one at the end of each record.

"JLGWhiz" wrote:

This does what you described, but I need to point out that it is based on
the assumption that each customer record consists of two lines of data
and
that you only want to delete the first line if it meet the conditions
that
you described. If that is not what you want, then re-define your problem
and re-post.

Also, this works from the bottom up, which prevents skipping any
consecutive
bland rows.

Sub chkforblank()
Dim lr As Long, i As Long, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
If lr Mod 2 = 0 Then
For i = lr To 1 Step -2
If sh.Cells(i - 1, 2) = "" And _
sh.Cells(i, 1) < "X" Then
Rows(i - 1).Delete
End If
Next
End If
End Sub



"MusicMan" wrote in message
...
I need a macro that will from A1 test if B1 is blank and if A2<"X"
delete
row A1, else move down and repeat.

I have a col of customer info in Col B which ahs extra blank rows
within.
An "X" in Col A denotes a new customer so the last line of a customer
info
should be blank hence the test above for A2

I was a wizz at Lotus macros but new to Excel VBA.

simple macro?
 

Hi "MusicMan"

Try the below and feedback...

Sub Hideemptyrows()
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Trim(Range("B" & lngRow)) = "" And _
Trim(UCase(Range("A" & lngRow + 1))) < "X" Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"MusicMan" wrote:

Restated. Delete line if col B is blank unless next row of a ="X"
Delete all excess blank lines
A B
1 X Name1
2 Add1
3 delete this and any other blanks lines
4 Add2
5 city
6 except this one as the next line is the next
cust with "X" in A
7 X Name2

"JLGWhiz" wrote:

It will be difficult to put it into a loop for deleting the rows unless
there is some factor in each variable length record that can be used to
identify the beginning and ending line. Or at least the beginning line of
each record if that is the one you want to delete. Computers are not that
smart, they have to be told what to look for. If you can think of some
consistent piece of data that can be used , re-post and we will give it
another stab.


"MusicMan" wrote in message
...
The number of lines per customer varies from 2 to 8. I'll look at this
and
see if it can be adapted. The problem is with the extra embedded blank
lines
other than one at the end of each record.

"JLGWhiz" wrote:

This does what you described, but I need to point out that it is based on
the assumption that each customer record consists of two lines of data
and
that you only want to delete the first line if it meet the conditions
that
you described. If that is not what you want, then re-define your problem
and re-post.

Also, this works from the bottom up, which prevents skipping any
consecutive
bland rows.

Sub chkforblank()
Dim lr As Long, i As Long, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
If lr Mod 2 = 0 Then
For i = lr To 1 Step -2
If sh.Cells(i - 1, 2) = "" And _
sh.Cells(i, 1) < "X" Then
Rows(i - 1).Delete
End If
Next
End If
End Sub



"MusicMan" wrote in message
...
I need a macro that will from A1 test if B1 is blank and if A2<"X"
delete
row A1, else move down and repeat.

I have a col of customer info in Col B which ahs extra blank rows
within.
An "X" in Col A denotes a new customer so the last line of a customer
info
should be blank hence the test above for A2

I was a wizz at Lotus macros but new to Excel VBA.