Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic newbie question | Excel Programming | |||
Real Newbie newbie question | New Users to Excel | |||
Newbie and Excel 2007 basic question | Excel Programming | |||
How to display unicode character in Visual Basic script. | Excel Programming | |||
Excel 2000/XP script to Excel97 script | Excel Programming |