Excel: Automating the Selection of an Unknown Number of Cells

Question

I’m trying to automate the formatting of an excel file by a macro and am seeking a solution.

I have two columns titled Last Name and First Name which I would like to concatenate into a separate column titled Last Name, First Name.

This is simple enough when done by hand: create one cell which does this, then drag that cell to include all cells within the range. The problem appears when trying to automate this. Because I can’t know the number of names that need to be concatenated ahead of time, I can’t automate the selection of cells by dragging.

Can you help me automate this?

I’ve tried a process of copying the initial concatenated cell, highlighting the column, and then pasting. I’ve also tried to use a formula which returned the concatenation only if there is text in the “Last Name” and “First Name” columns. However, in both cases, I end up with some 100,000 rows, putting a serious cramp on my ability to manipulate the worksheet.

The best solution I can think of is to create concatenations within a fixed range of cells. Although this would create useless cells, at least there wouldn’t be 99,900 of them.

Normally you would use `Find` or the `xlUp` like finding techniques to define your range (`Find` is more versatile). See http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba/8583926#8583926 — brettdj, Dec 14 '12 at 23:00

score 1 · Answer 1 · answered Dec 14 '12 at 20:22

1

The UsedRange property can be used to figure out how many rows/columns need selecting on a sheet with an unknown about of data. Here's an example.

answered Dec 14 '12 at 20:22

DWright

9,258
4
36
53

score 1 · Answer 2 · answered Dec 14 '12 at 20:28

You can make Excel automatically add formula to the field if required. Just fill three sells: First name - Last name - Formula. Then select the range and format it as a table (Home -> Style -> Format as Table) and Excel with automatically add formula for the third field if you put something in first of last name.

score 0 · Answer 3 · answered Dec 14 '12 at 20:18

0

Can't you use in your C1 formula =A1&" "&B1 and them double click the black square and expand the function? And then Apply Filter?

answered Dec 14 '12 at 20:18

Andrew

7,619
13
63
117

score 0 · Answer 4 · answered Dec 14 '12 at 20:24

Here an example how to get your rows!

Sub ExampleNames()
Dim MaxRow As Long
Dim i As Long

MaxRow = Worksheet(1).Cells(1, 1).CurrentRegion.Rows.Count  'For Excel 2010 and later

' OR

'MaxRow = Worksheets(1).Cells(1.1).UsedRange.Rows.Count  'For prior Excel Verisons

For i = 1 To MaxRow
    ' your code to string them together
Next i

End Sub

Excel: Automating the Selection of an Unknown Number of Cells

4 Answers4