Auto delete contents
 

Ok, I have a sheet which has a ton of data. I want to delete data only
from 2002-2006, the whole row to be deleted if column F says anything
other than IND
Thanks

Auto delete contents
 

Hi,

Sub Delete_Rows()

Dim rngF As Range
Dim c As Range
Dim i As Long

Set rngF = ActiveSheet.Range(Cells(1, "F"), _
Cells(Rows.Count, "F").End(xlUp))

'Work backwards from bottom to top when deleting rows
With rngF
For i = .Rows.Count To 1 Step -1
If .Cells(i) < "IND" Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub


Regards,

OssieMac

" wrote:

Ok, I have a sheet which has a ton of data. I want to delete data only
from 2002-2006, the whole row to be deleted if column F says anything
other than IND
Thanks

Auto delete contents
 

On Oct 9, 8:14 pm, OssieMac
wrote:
Hi,

Sub Delete_Rows()

Dim rngF As Range
Dim c As Range
Dim i As Long

Set rngF = ActiveSheet.Range(Cells(1, "F"), _
Cells(Rows.Count, "F").End(xlUp))

'Work backwards from bottom to top when deleting rows
With rngF
For i = .Rows.Count To 1 Step -1
If .Cells(i) < "IND" Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub

Regards,

OssieMac



" wrote:
Ok, I have a sheet which has a ton of data. I want to delete data only
from 2002-2006, the whole row to be deleted if column F says anything
other than IND
Thanks- Hide quoted text -

- Show quoted text -

I'm sorry but that deleted the entire sheet

Auto delete contents
 

It works under test with me so I must assume that the cells in column F which
are not to be deleted do not have IND in them. Perhaps they have some other
characters before or after the IND. (These could be spaces or some other
undisplayed characters).

I hope that you didn't save the workbook and that you did not loose your data.

The following searches for the string IND in the cell so will not matter if
there are other characters also.

Sub Delete_Rows()

Dim rngF As Range
Dim c As Range
Dim i As Long

Set rngF = ActiveSheet.Range(Cells(1, "F"), _
Cells(Rows.Count, "F").End(xlUp))

'Work from bottom when deleting rows
With rngF
For i = .Rows.Count To 1 Step -1
If InStr(1, .Cells(i), "IND") = 0 Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub


Regards,

OssieMac

Auto delete contents
 

My comment "I hope that you didn't save the workbook and that you did not
loose your data" should have said:-
I hope that you didn't save the workbook (after running the macro) and that
you did not loose your data and were able to recover it.

Regards,

OssieMac


"OssieMac" wrote:

It works under test with me so I must assume that the cells in column F which
are not to be deleted do not have IND in them. Perhaps they have some other
characters before or after the IND. (These could be spaces or some other
undisplayed characters).

I hope that you didn't save the workbook and that you did not loose your data.

The following searches for the string IND in the cell so will not matter if
there are other characters also.

Sub Delete_Rows()

Dim rngF As Range
Dim c As Range
Dim i As Long

Set rngF = ActiveSheet.Range(Cells(1, "F"), _
Cells(Rows.Count, "F").End(xlUp))

'Work from bottom when deleting rows
With rngF
For i = .Rows.Count To 1 Step -1
If InStr(1, .Cells(i), "IND") = 0 Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub


Regards,

OssieMac