Delete Rows & Capture Range
 

hi,
i have the following code:

For Each c In Range("H1:H1000")
If c = 0 Then c.EntireRow.Delete
Next

i need to delete rows in which the value is 0 for column H..
the above code does not work for some reason....

also,i need to capture the range correctly..coz the actual number o
rows is different at different types..how do i capture the range an
delete the rows having column value 0......??

--
Message posted from http://www.ExcelForum.com

Delete Rows & Capture Range
 

Hi

Try this for the first 1000 rows of the activesheet

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "H").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "H").Value = "0" Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"sameer27p " wrote in message ...
hi,
i have the following code:

For Each c In Range("H1:H1000")
If c = 0 Then c.EntireRow.Delete
Next

i need to delete rows in which the value is 0 for column H..
the above code does not work for some reason....

also,i need to capture the range correctly..coz the actual number of
rows is different at different types..how do i capture the range and
delete the rows having column value 0......???


---
Message posted from http://www.ExcelForum.com/

Delete Rows & Capture Range
 

Hi
you have to work from the bottom to the top. in your case for example
try:

Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For row_index = lastrow To 1 Step -1
If Cells(row_index, "H").Value =0 then
Rows(row_index).delete
End If
Next
Application.ScreenUpdating = True
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany


hi,
i have the following code:

For Each c In Range("H1:H1000")
If c = 0 Then c.EntireRow.Delete
Next

i need to delete rows in which the value is 0 for column H..
the above code does not work for some reason....

also,i need to capture the range correctly..coz the actual number of
rows is different at different types..how do i capture the range and
delete the rows having column value 0......???


---
Message posted from http://www.ExcelForum.com/

Delete Rows & Capture Range
 

Hi,

Sub Delete_Rows()

Dim CL As Range
Dim GG As Range

With Application
.Calculation = xlManual
.ScreenUpdating = False
End With

For Each CL In Range("H1:H1000")
If CL.Value = 0 Then
If GG Is Nothing Then Set GG = CL Else Set GG = Union(GG, CL)
End If
Next

GG.EntireRow.Delete

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With

End Sub


--
Regards,
Soo Cheon Jheong
_ _
^ ^
v