Selecting Rows in a Macro

wallyign

New Member
Joined
Jan 2, 2005
Messages
4
Hi

I receive a excel spreadsheet every two weeks that I use a Macro to reformat before I can import it into Access

I use the Concatenate Function to group 4 columns into One with '-' between the data from each column.

When I run the Macro, it groups the columns fine but it does not stop at the end of the last active row in the spreadsheet. Which results in a spreadsheet with - - - in that column after the last active row to row 64000


How can you select a column to include only the active cells within a Macro

Any help would be appreciated

Wally
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Welcome to the Board!

You could use something like this to select the range of interest
Code:
Range("A1", Range("A65536").End(xlUp))
The required range is therefore A1 to the last cell containing data in column A.

Or perhaps assign a variable to the last row,
Code:
iRow = Range("A65536").End(xlUp).Row
You can then use that variable in your code
Code:
For x = 1 To iRow
'do your concatenate stuff here
Does this help?

Regards
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello & welcome to the board.
What Glaswegian posted works very well and is what I use most often, if I know which column will have the highest row number as the last used cell (or if I know they're all the same).
However if you have some columns that use more rows than others and/or you don't always know which one will be the longest, you can use this for your variable.
Code:
Dim Lr As Long
Lr = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For x = 1 To Lr 
'do your concatenate stuff here
This will refer to the last used row in the sheet no matter what column it's found in.

Hope it helps,
Dan
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
A good reminder Dan - must remember to include that next time I see a similar question!
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259

ADVERTISEMENT

This soln assumes that the Strings to Concantenate are in Columns A,B,C,D and puts the results in Col E.

Sub ConcStrng()
For Each Strng In Columns("A").SpecialCells(xlCellTypeConstants, 3)
With Strng
.Offset(0, 5) = .Value & "-" & .Offset(0, 1) & "-" & .Offset(0, 2) & "-" & .Offset(0, 3)
End With
Next Strng
End Sub
 

wallyign

New Member
Joined
Jan 2, 2005
Messages
4
Thanks for quick reply

All the solutions look great. I think I will try Dan's idea because each sheet I receive contains a different number of rows.

Thanks
Wally
 
L

Legacy 1363

Guest

ADVERTISEMENT

Intersect([A:A], ActiveSheet.UsedRange).Offset(, 5).FormulaR1C1 = _
"=RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi Wally,
All the solutions provided will work on sheets with a different number of rows.
My idea only comes into play if all the columns won't have the same number of rows.

Dan
 
L

Legacy 1363

Guest
HalfAce said:
Hi Wally,
All the solutions provided will work on sheets with a different number of rows.
My idea only comes into play if all the columns won't have the same number of rows.

Dan

Ditto my suggestion.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top