Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row
Am trying to have this request a date from the user, and then delete all
rows of data prior to that date. Any suggestions? I get a run time error on the a.EntireRow.Delete code. Sub DeleteDates() Dim TempString As String TempString = InputBox("Enter beginning Date", "Beginning of Date Range") If Not TempString = "" Then msg = "Removing Data prior to - " & TempString & " , Are you Sure this is the Correct Date?" DialogStyle = vbYesNo + vbExclamation + vbDefaultButton1 Title = "Is this the Correct Date?" Response = MsgBox(msg, DialogStyle, Title) End If If Response = vbYes Then GoTo DeleteData Else GoTo Aborted End If DeleteData: For a = 1000 To 2 Step -1 If Cells(2, a).Value < TempString Then a.EntireRow.Delete Next a Aborted: End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row
Hi John,
a is a row number your formula is invalid try Cells(2,a).EntireRow.Delete or a little better Rows(a).Delete I would recommend that you DIMension your variables. For a = 1000 To 2 Step -1 If Cells(2, a).Value < TempString Then a.EntireRow.Delete --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "John Keturi" wrote in message news:VeZhd.91854$hj.32175@fed1read07... Am trying to have this request a date from the user, and then delete all rows of data prior to that date. Any suggestions? I get a run time error on the a.EntireRow.Delete code. Sub DeleteDates() Dim TempString As String TempString = InputBox("Enter beginning Date", "Beginning of Date Range") If Not TempString = "" Then msg = "Removing Data prior to - " & TempString & " , Are you Sure this is the Correct Date?" DialogStyle = vbYesNo + vbExclamation + vbDefaultButton1 Title = "Is this the Correct Date?" Response = MsgBox(msg, DialogStyle, Title) End If If Response = vbYes Then GoTo DeleteData Else GoTo Aborted End If DeleteData: For a = 1000 To 2 Step -1 If Cells(2, a).Value < TempString Then a.EntireRow.Delete Next a Aborted: End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row
"John Keturi" schreef in bericht
news:VeZhd.91854$hj.32175@fed1read07... Am trying to have this request a date from the user, and then delete all rows of data prior to that date. Any suggestions? I get a run time error on the a.EntireRow.Delete code. Sub DeleteDates() Dim TempString As String TempString = InputBox("Enter beginning Date", "Beginning of Date Range") If Not TempString = "" Then msg = "Removing Data prior to - " & TempString & " , Are you Sure this is the Correct Date?" DialogStyle = vbYesNo + vbExclamation + vbDefaultButton1 Title = "Is this the Correct Date?" Response = MsgBox(msg, DialogStyle, Title) End If If Response = vbYes Then GoTo DeleteData Else GoTo Aborted End If DeleteData: For a = 1000 To 2 Step -1 If Cells(2, a).Value < TempString Then a.EntireRow.Delete Next a Aborted: End Sub You are referring to Cells(2,a), which means (2,1000) the first time. But Excel has only 256 columns so I think you're confusing rows and columns and columns and rows. Maybe Cells(a,2) works better... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row
Wow. Fast Usenet refresh I have...
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row
You are referring to Cells(2,a), which means (2,1000) the first time. But Excel has only 256 columns so I think you're confusing rows and columns and columns and rows. Maybe Cells(a,2) works better... missed that and even repeated the mistake. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |