Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fit contents | Excel Discussion (Misc queries) | |||
delete contents of cell | Excel Discussion (Misc queries) | |||
Delete cell contents | Excel Discussion (Misc queries) | |||
Delete Cell Contents IF | Excel Programming | |||
How to Delete Contents of D2 if B2 = "SD" | New Users to Excel |