Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Count is not in a certain range, delete Rows | Excel Discussion (Misc queries) | |||
Select a range of rows to delete | Excel Worksheet Functions | |||
delete rows in range - macro | Excel Worksheet Functions | |||
Delete all Rows in a Variable Range | Excel Programming | |||
Delete Duplicate rows in a range | Excel Programming |