PDA

View Full Version : Solved: Help with loop please



Blackie50
02-15-2011, 03:47 AM
Hi

Need to start at row 5 and then test each cell (in a variable used range) in column I to see if it is 0 (zero) - if it is then delete that row.

Any help appreciated

thanks
Jon

GTO
02-15-2011, 04:19 AM
Greetings Jon,

Does this help?

Option Explicit

Sub exa_BlanksCountAsZero()
Dim wksActiveSheet As Worksheet
Dim i As Long

'//For example only... //
Set wksActiveSheet = ActiveSheet
'// Set a reference using the worksheet's (tab) name //
'Set wksActiveSheet = ThisWorkbook.Worksheets("Sheet1")
'// OR:
'// ...simply use the sheet's codename if the code will always//
'// run against the same sheet. //
'With Sheet1

With wksActiveSheet
For i = .Cells(.Rows.Count, 9).End(xlUp).Row To 5 Step -1
'// Blank cells will count as zero//
If .Cells(i, 9).Value = 0 Then
.Cells(i, 9).EntireRow.Delete xlShiftUp
End If
Next
End With
End Sub

Sub exa_SkipBlanks()
Dim wksActiveSheet As Worksheet
Dim i As Long

Set wksActiveSheet = ActiveSheet
With wksActiveSheet
'// Test that the cell's val equals zero, but the cell //
'// is NOT blank. //
For i = .Cells(.Rows.Count, 9).End(xlUp).Row To 5 Step -1
If .Cells(i, 9).Value = 0 _
And Not .Cells(i, 9).Value = vbNullString Then
.Cells(i, 9).EntireRow.Delete xlShiftUp
End If
Next
End With
End Sub

Mark

IBihy
02-15-2011, 04:36 AM
Hello Jon,
assuming "variable used range" means a range that has a variable amount of rows. And I'm guessing the loop in itself is not the problem here, rather determining the end of a range dynamically.
Under the condition that the range is contiguous (i.e. no empty rows in between) you can determine the size of your range as follows:
Dim WS As Worksheet
Dim myRange As Range
Dim lngRowCt As Long
Dim lngaRow as Long
Set WS = ThisWorkbook.Worksheets("Sheet1") 'or any other sheet name
myRange = WS.Range("I5").CurrentRegion
lngRowCt = myRange.Rows.Count
"CurrentRegion" has a catch, though: If the columns to the right and left of the column to determine the current region from are filled, then the current region includes them all.
Now use the above stuff in a loop:
For lngaRow = 1 to lngRowCt
If myRange.Cells(lngaRow, 1).Value = 0 Then
myRange.Rows(lngaRow).Delete
End If
Next lngaRow

HTH,
Isabella

Blackie50
02-15-2011, 04:57 AM
Thanks Guys - your help is much appreciated.

GTOs solution works fine.

There is data in adjoining cells so probably can't use IBihys solution

thanks again
Jon