Loop ?

[chrisnsmith]

I tried using the following code to update the forms below, but I obviously
have something wrong because it does nothing. What I want to do
is delete Row in which Column C is less than 1, and then shift
the next row following the deleted row to shift up to the blank row.
I only tried it for Columns A,B and C.
What do I need to do?

I know I'm asking someone to write my code, but I'm new to Excel and VB
and could sure use some help.


Sub UpdateForm()

Set CurrentRange = Range("A14:C14")

Do Until Range("C14") > 0
Set NextRange = CurrentRange
If Range("C14") < 1 Then
CurrentRange.EntireRow.Delete
End If
Set CurrentRange = NextRange

Loop

End Sub


A B C G H
I
Account Date Amount Account Date Amount
14 cd 2/13/2009 4 bf 2/13/2009 6
15 wf 2/13/2009 3 af 2/13/2009 2
16 we 2/13/2009 56 gf 2/13/2009 34
17 qe 2/13/2009 23 bg 2/13/2009 16
18 rt 2/13/2009 0 fg 2/13/2009 0
19 yu 2/13/2009 23 yk 2/13/2009 45
20 ty 2/13/2009 8 lu 2/13/2009 2
21 ui 2/13/2009 9 tq 2/13/2009 8
22 op 2/13/2009 0 de 2/13/2009 0
23 th 2/13/2009 34 wc 2/13/2009 11
24 bn 2/13/2009 2 bz 2/13/2009 98
25 cv 2/13/2009 1 hm 2/13/2009 1

 

[Dave Peterson]

Option Explicit
Sub Testme()
dim iRow as long
dim LastRow as Long
dim FirstRow as long

with worksheets("sheet1") '<-- change the name here!
firstrow = 2
lastrow = .cells(.rows.count,"C").end(xlup).row

'start at the bottom and work up
for irow = lastrow to firstrow step -1
if .cells(irow,"C").value < 1 then
.rows(irow).delete
end if
next irow
end with

end sub

 

[chrisnsmith]

That did just what I said I wanted, but what I said is not what I meant.
(Heard that before). What I actually need to do is clear the contents of the
in each row where
column C is less than 1 and shift the next row up to replace the cleared row
and still maintain the same amount of rows.

 

[chrisnsmith]

Forgot one thing. I need to do the same with the Columns G,H and I.

 

[Don Guillett]

Sub cleariflessthanzero()
Dim i As Long
For i = Cells(Rows.Count, "c").End(xlUp).Row To 1 Step -1
If Cells(i, "c") < 1 Then Cells(i, "c").Delete Shift:=xlUp
Next i
End Sub

 

[Don Guillett]

Actually, looking at your data, it appears that you want to delete the cells
that PERTAIN to the 0 cell, so

To look at only ONE column
Sub cleariflessthanzeroOffset()
Dim mc, i As Long
mc = 3 '"c"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 32 Step -1
If Cells(i, mc) < 1 Then Cells(i, mc - 2).Resize(, 3).Delete Shift:=xlUp
Next i
End Sub

To look at columns 3 (c), 9 (i)
Sub cleariflessthanzeroOffsetLoopcols()
Dim mc, i As Long
For Each mc In Array(3, 9) 'column numbers
For i = Cells(Rows.Count, mc).End(xlUp).Row To 32 Step -1
If Cells(i, mc) < 1 Then Cells(i, mc - 2).Resize(, 3).Delete Shift:=xlUp
Next i
Next mc
End Sub

 

[chrisnsmith]

Some additional info.
Columns G,H and I should be considered extentios of Columns A,B and C. The
rows in each section are actually 50 lines. The worksheet is set up this way
for printing
purposes. So with any cells in column C that are < 1 the rows in both
sections need to shift up one. In other words if row 50 in Columns A,B and C
is < 1 and is cleared then row 14 in Columns G,H and I needs to shift to
Columns A,B and C and so on.