Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro?
Why does the following macro jump to End Sub after it encountered the first
cell with 0 (zero) value instead of deleting the entire row? Values to be tested are in column B Sub RemoveRow() Range("B2:B30").Select Do Until ActiveCell.Value = 0 If ActiveCell.Value = 0 Then ActiveCell.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro?
Use the followig...
Sub RemoveRow() Range("B2:B30").Select Do Until ActiveCell = "" If ActiveCell.Value = 0 Then ActiveCell.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop End Sub Your version was ending as the Do Until was waiting for the value 0. Rob Edwards Always look on the bright side of life! *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro?
Because
Do Until ActiveCell.Value = 0 exits the loop before the rest of the code is executed. A different way: Dim rCell As Range Dim rDelete As Range For Each rCell In Range("B2:B30") If rCell.Value = 0 Then If rDelete Is Nothing Then Set rDelete = rCell Else Set rDelete = Union(rDelete, rCell) End If End If Next rCell If Not rDelete is Nothing then rDelete.EntireRow.Delete A bit more code, but much faster. In article , Tatebana wrote: Why does the following macro jump to End Sub after it encountered the first cell with 0 (zero) value instead of deleting the entire row? Values to be tested are in column B Sub RemoveRow() Range("B2:B30").Select Do Until ActiveCell.Value = 0 If ActiveCell.Value = 0 Then ActiveCell.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro?
The reason it's jumping to End Sub is because when you selected B2:B30, there
is not just one cell selected. The ActiveCell object was empty. Try it this way: Sub RemoveRow() Dim lRow As Long For lRow = 30 To 2 Step -1 With ActiveSheet.Cells(lRow, 2) If .Value = 0 Then .EntireRow.Delete End If End With Next lRow End Sub Note that the above will delete the entire row if the cell in B column has a 0 value, or if it's blank. If you only want to delete rows where there is an explicit 0 value entered, try it this way: Sub RemoveRow() Dim lRow As Long For lRow = 30 To 2 Step -1 With ActiveSheet.Cells(lRow, 2) If .Text = "0" Then .EntireRow.Delete End If End With Next lRow End Sub Also note that both loops are going from bottom to the top to make sure you don't miss any rows. -- Hope that helps. Vergel Adriano "Tatebana" wrote: Why does the following macro jump to End Sub after it encountered the first cell with 0 (zero) value instead of deleting the entire row? Values to be tested are in column B Sub RemoveRow() Range("B2:B30").Select Do Until ActiveCell.Value = 0 If ActiveCell.Value = 0 Then ActiveCell.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro?
Absolutely not!
The ActiveCell ALWAYS is assigned as long as a workbook is Active. When multiple cells are selected, the ActiveCell is whichever cell has the focus. It's still populated even if a Shape is selected. In article , Vergel Adriano wrote: The reason it's jumping to End Sub is because when you selected B2:B30, there is not just one cell selected. The ActiveCell object was empty. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro?
Agree! I shouldn't have assumed and said that. I don't use ActiveCell that
often and when I debugged the code, I didn't realize B2 was blank which of course ActiveCell would be empty. Thanks! -- Vergel Adriano "JE McGimpsey" wrote: Absolutely not! The ActiveCell ALWAYS is assigned as long as a workbook is Active. When multiple cells are selected, the ActiveCell is whichever cell has the focus. It's still populated even if a Shape is selected. In article , Vergel Adriano wrote: The reason it's jumping to End Sub is because when you selected B2:B30, there is not just one cell selected. The ActiveCell object was empty. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple macro | Excel Discussion (Misc queries) | |||
Simple Macro | Excel Programming | |||
Simple macro help | Excel Discussion (Misc queries) | |||
Simple Macro Help | Excel Programming | |||
Simple help with macro please | Excel Programming |