VBA - how to get row number?

**hegisin** · 02-21-2007, 01:13 PM

Hi,

I'm an absolute beginner in VBA and I'm trying the learning by doing method.

Can somehow help me pleae with this simple problem:

After having imported a text file into excel I have something like that:

Row Content
1 HOUSE
2 MOUSE
3 XXX
4 SB73
5 KLM
6 ICON
7 SHOE88
8 XXX
9 78
10 25 MILES
11 FLOOD
12 XXX
13 AIR86PLANE
ETC
ETC

Now I want to export (copy and paste) the arrays which are in between "xxx" to a new sheet. First array should be placed at A1, second at E1 third at A1+20 fourth et E1+20, fifth at A1+40, sixth at E1+40 etc.

How would I do that?

My Idea is to find out the row number of the "xxx" and then copy and paste the rows in between. For example the second "xxx" is at row number 8 and the first xxx is at row number three. Therefore I know that I have to copy and paste 4 cells (8-3+1) starting from row number 4 (3+1). How to find out at which rom number the xxx's are located

Thanks for your help.

Cheers,
hegisin

**Carim** · 02-21-2007, 01:58 PM

Hi,

The instruction to get the row number is ...

Please Login or Register  to view this content.

**hegisin** · 02-21-2007, 02:13 PM

Thanks for that: so simple

However, I was not sure as "output = ActiveCell.row.value" returns an error message.

**hegisin** · 02-21-2007, 03:22 PM

So now, since I know how to extract the row numbers, how can I use them for the selection of the range. For instance: Second result of "xxx" is 8 and first is 3. Now I want to use these two number to select my range: range(A4:A7)?

A4 (3+1) and A7 (8-1), because I want to select the cells in between the xxx's.

Any idea?

**jasoncw** · 02-21-2007, 05:46 PM

How are you trapping the values of these two row numbers, in different variables? Assuming you are, try this:

Please Login or Register  to view this content.

HTH

Jason

**hegisin** · 02-21-2007, 11:26 PM

Hi Jason,

I feel I'm coming close now. Thanks for your help.

However, If I try your code than it returns an error message: "Object required" and points to "Myrng".

What am I doing wrong?

Cheers,
hegisin

**jasoncw** · 02-21-2007, 11:27 PM

Did you declare Myrng as a variable?

i.e. dim Myrng As Range

**hegisin** · 02-22-2007, 01:44 AM

Yes I did dim myrng but yet I got the erro message.

Also please advise what do I have to write in between istart and iend to identify "xxx".

Sorry for my dumb questions, but I'm really a newbie.

I appreciate your effort.

Cheeers,
hegisin

VBA - how to get row number?

LinkBack

Thread Tools

Rate This Thread

Display

VBA - how to get row number?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions