Looping Macro to remove data where value is 0

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I currently have a speadsheet which has been set up with
various heading across the page and reference numbers as
data down the first column. When goods have been
received, a value is entered in the column heading and
against the ref number.The sheet is updated daily
At the end of the week I am looking to condence the
information to only show data with values. The filter
cannot work - Can you help
 
Sub DeleteemptyRows()
Dim rng as Range, rng1 as Range
Dim LastRow as Long
set rng = cells(2,2)
lastrow = cells(rows.count,1).End(xlup)

do while rng.row <= Lastrow
if application.countA(rng.Resize(1,255)) = 0 then
if rng1 is nothing then
set rng1 = rng
Else
set rng1 = Union(rng1,rng)
end if
end if
set rng = rng.Offset(1,0)
Loop
if not rng1 is nothing then
rng1.EntireRow.Delete
End if
End sub

Would be one interpretation of what you describe.
 
I read the problem differently from Tom and am kinda simple.

dim i as integer
dim lastrow as integer
lastrow = cells(rows.count,1).End(xlup)

While i <= lastrow
If amt + Sheet1.Columns("B").Rows(i).Value<> 0 Then
i = i + 1 'set i to next row
Else 'delete row
Rows(i & ":" & i).Select
Selection.ClearContents
Selection.Delete Shift:=xlUp
End If
Wen
 
Back
Top