Detele Rows in a range

[Chandresh]

Hi All,

Below macro is working fine and deleting the data till the last row in the excel , could you please help me if the same macro can delete ony the data in a range F6:F4009


Sub testdelcells()
Windows("Chan.xlsm").Activate
Sheets("Test").Select
With ActiveSheet
.AutoFilterMode = False
With Range("F6", Range("F" & Rows.Count).End(xlUp))
.AutoFilter 1, "*DUMMY*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.delete
End With
.AutoFilterMode = False
End With
End Sub


Thanks
Chand

 

[Zot]

I think you just need to replace the word Delete with Clear. If you want to keep formula in cells, use ClearContents instead.

 

[Chandresh]

sorry I want to delete entire row if it contain " DUMMY" in range F6:F4009

 

[Zot]

Meaning if any row contains DUMMY, the entire row is deleted. Therefore the range probably reduced in number of rows, correct?

Is there anything else beyond F4009 that you do not want to delete?

 

[Chandresh]

yes there are data beyond F4009 but we don't want to delete it (we will not have DUMMY after F4009, DUMMY will be only in the range F6:F4009)

 

[Zot]

Then you must find out how many rows get deleted.
1) Get number of rows from F6 to the end
2) Perform DUMMY delete
3) Get the number of rows from F6 to the end
4) Redefine range F6:Fx
4) ClearContents in that new defined range

Your code

Sub testdelcells()
Windows("Chan.xlsm").Activate
Sheets("Test").Select
With ActiveSheet
.AutoFilterMode = False
With Range("F6", Range("F" & Rows.Count).End(xlUp))
.AutoFilter 1, "*DUMMY*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.delete
End With
.AutoFilterMode = False
End With
End Sub

Looking back at your code above, looks like you filter data from F6 until end of all rows, not until F4009. Am I missing something here? Probably you do not have DUMMY after F4009.

Anyway, you can find total rows by adding bold lines
With Range("F6", Range("F" & Rows.Count).End(xlUp))
m=.Rows.Count
.AutoFilter 1, "*DUMMY*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.delete
.AutoFilterMode = False ' this line is relocated
n=.Rows.Count
End With
Range("F6", Range("F" & (m-n)).ClearContents
End Sub

Not test.

 

[Chandresh]

thanks I will try

 

[MARK858]

@Chandresh, try

Sub testdelcells()
    Windows("Chan.xlsm").Activate
    With Sheets("Test")
        .AutoFilterMode = False
        With .Range("F5:F4009")
            .AutoFilter 1, "*DUMMY*"
            On Error Resume Next
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub

If you want to keep formula in cells, use ClearContents instead.

@Zot ClearContents removes the formula as well, it doesn't clear formatting.

 

[Zot]

@Chandresh, try

Sub testdelcells()
    Windows("Chan.xlsm").Activate
    With Sheets("Test")
        .AutoFilterMode = False
        With .Range("F5:F4009")
            .AutoFilter 1, "*DUMMY*"
            On Error Resume Next
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub

@Zot ClearContents removes the formula as well, it doesn't clear formatting.

? My bad. Thanks for correcting.