Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro auto insert/delete
Afternoon,
I am trying to use a macro that will delete any row that has a cell with the value of "#VALUE!" in it. I have tried several macro examples found on this and other websites, one such one like this: ----------- Sub DeleteRows() Dim strToDelete As String Dim rngSrc As Range Dim NumRows As Integer Dim ThisRow As Integer Dim ThatRow As Integer Dim ThisCol As Integer Dim J As Integer Dim DeletedRows As Integer strToDelete = "#VALUE!" Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address) NumRows = rngSrc.Rows.Count ThisRow = rngSrc.Row ThatRow = ThisRow + NumRows - 1 ThisCol = rngSrc.Column For J = ThatRow To ThisRow Step -1 If Cells(J, ThisCol) = strToDelete Then Rows(J).Select Selection.Delete Shift:=xlUp DeletedRows = DeletedRows + 1 End If Next J MsgBox "Number of deleted rows: " & DeletedRows End Sub ---------------- Except the line containing "strToDelete = "#VALUE!"" throws an error and the program will not continue. It works fine if I use "strToDelete = "bob"". I looked up an escape character, it seems to be " so I tried ""#VALUE"!" and many other variants, no success. Any suggestions on how to delete rows that contain #VALUE! ? Thank you for taking your time to read and respond. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro auto insert/delete
Sorry, Excel 2003.
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro auto insert/delete
I would like to apologize, after I posted I figured out the answer:
Delete all rows with the specified cell that is not numeric! Have a great day. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro auto insert/delete
or...
If Cells(J, ThisCol).Text = strToDelete Then Eric wrote: Afternoon, I am trying to use a macro that will delete any row that has a cell with the value of "#VALUE!" in it. I have tried several macro examples found on this and other websites, one such one like this: ----------- Sub DeleteRows() Dim strToDelete As String Dim rngSrc As Range Dim NumRows As Integer Dim ThisRow As Integer Dim ThatRow As Integer Dim ThisCol As Integer Dim J As Integer Dim DeletedRows As Integer strToDelete = "#VALUE!" Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address) NumRows = rngSrc.Rows.Count ThisRow = rngSrc.Row ThatRow = ThisRow + NumRows - 1 ThisCol = rngSrc.Column For J = ThatRow To ThisRow Step -1 If Cells(J, ThisCol) = strToDelete Then Rows(J).Select Selection.Delete Shift:=xlUp DeletedRows = DeletedRows + 1 End If Next J MsgBox "Number of deleted rows: " & DeletedRows End Sub ---------------- Except the line containing "strToDelete = "#VALUE!"" throws an error and the program will not continue. It works fine if I use "strToDelete = "bob"". I looked up an escape character, it seems to be " so I tried ""#VALUE"!" and many other variants, no success. Any suggestions on how to delete rows that contain #VALUE! ? Thank you for taking your time to read and respond. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto insert macro | Excel Discussion (Misc queries) | |||
Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
how do I create a macro to auto insert rows? | Excel Worksheet Functions | |||
how to insert row or delete while the auto filter is turn on? | Excel Discussion (Misc queries) | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) |