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

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

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...

Delete a row
 

Wow. Fast Usenet refresh I have...

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.