A basic VB script--help the newbie?
 

Hi,

I think I got the wrong books for learning how to program in VBA...

I need to do the following:

if cell below current cell has the same value
and the three cells to the right of the cell below current cell have
no value
delete entire row of the cell below the current cell
repeat until different value in next cell down found

EX:

11FF07 54.90 58.90 64.23
11FF07 (blank) (blank) (blank)
11FF07 (blank) (blank) (blank)
11C 56.98 123.34 57.78

So this macro/script would see that the next 11FF07 down has no prices
(blank values) in the three cells next to it, and delete that row, and
continue down, see that the next 11FF07 down has no value either,
delete that row, continue down, and notice the next cell contains a
different value and stop.

Can anyone help with this? This isn't something I can figure out how
to "tell" the macro recorder to do with actions.

Thanks,
Gabe

A basic VB script--help the newbie?
 

Gabe,

Here's one way. Make sure you backup your file first just in case it doesn't
do what you want. The script will work from bottom to top. Check if the
value in the current cell is equal to the one above. If yes, it checks if
the 3 cells to the right are blank. If yes, the entire row is deleted.

Sub test()
Dim lRow As Long
Dim lLastRow As Long

lLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row

For lRow = lLastRow To 2 Step -1
With Range("A" & lRow)
If .Value = .Offset(-1, 0).Value Then
If .Offset(0, 1).Value & .Offset(0, 2) & .Offset(0, 3) = ""
Then
.EntireRow.Delete
End If
End If
End With
Next lRow

End Sub

--
Hope that helps.

Vergel Adriano


"comp.databases.pick" wrote:

Hi,

I think I got the wrong books for learning how to program in VBA...

I need to do the following:

if cell below current cell has the same value
and the three cells to the right of the cell below current cell have
no value
delete entire row of the cell below the current cell
repeat until different value in next cell down found

EX:

11FF07 54.90 58.90 64.23
11FF07 (blank) (blank) (blank)
11FF07 (blank) (blank) (blank)
11C 56.98 123.34 57.78

So this macro/script would see that the next 11FF07 down has no prices
(blank values) in the three cells next to it, and delete that row, and
continue down, see that the next 11FF07 down has no value either,
delete that row, continue down, and notice the next cell contains a
different value and stop.

Can anyone help with this? This isn't something I can figure out how
to "tell" the macro recorder to do with actions.

Thanks,
Gabe