--- In ExcelVBA@yahoogroups.com, "mickey11030" wrote:
>
> In a For loop I examine each cell. I want to delete rows that do not contain a particular string. I didn't know how to delete a row, so I recorded a macro. When I arbitrarily deleted row 21, the recorder gave these statements
>
> Rows("21:21").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlUp
>
> I assumed that to delete Row J, (J is the counter for the loop) I simply had to replace the first statement with
>
> Rows("J:J").Select
>
> but that gave an "Application-defined or object-defined error". I checked that J had a valid value, 23, in this case.
>
> 10 minutes later
>
> In desperation I replaced the Rows statement with
>
> Row(J).Select
>
> and that worked. So now I know how to delete a row, but can anyone explain why the statement that mirrored the one generated by the macro recorder, Rows("J:J").Select, didn't work.
>
>
> Thanks
> Michael Trombetta
>
If you want to remove the row using a variable in a loop use something like the example below. If you use quotation marks in your reference to a column it means literally the column of that letter. For a row you would need to use the row number right?
The example below examines the value in cells A1 to A50 and if the value in any of these cells is > 100 then the row is deleted.
For i = 1 To 50
If Range("A" & i) > 100 Then
Rows(i).EntireRow.Delete
End If
Next
Hope this helps
Daniel
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar