Re: [ExcelVBA] How Do I Delete a Row?

 

> Rows("J:J").Select

is looking for a row called J. You need the variable J.

> Rows(J&":"&J).Select

WARNING Don't forget that a delete of a row will end up with all future
rows being one higher than before. I.e. do not use the counter from an
ascending loop, as it'll either delete the wrong rows, or skip a row.

If you want to delete rows from a loop, then loop backwards from the bottom.

Regards, Dave S

----- Original Message -----
From: "mickey11030" mickey11030@yahoo.com>
To: ExcelVBA@yahoogroups.com>
Sent: Sunday, February 17, 2013 9:20 AM
Subject: [ExcelVBA] How Do I Delete a Row?

> 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
>
>
>
> ------------------------------------
>
> ----------------------------------
> 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
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1430 / Virus Database: 2639/5606 - Release Date: 02/15/13
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 3

Visit Your Group

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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___