Selecting Rows in a Macro

[wallyign]

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

 

[Glaswegian]

Welcome to the Board!

You could use something like this to select the range of interest

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,

iRow = Range("A65536").End(xlUp).Row

You can then use that variable in your code

For x = 1 To iRow
'do your concatenate stuff here

Does this help?

Regards

 

[HalfAce]

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.

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]

A good reminder Dan - must remember to include that next time I see a similar question!

 

[Nimrod]

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]

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

 

[Legacy 1363]

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

 

[HalfAce]

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

 

[Legacy 1363]

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.