Selecting Multiple Columns Using Header names - Excel VBA

Question

I have a requirement where I need to select Multiple columns using only the header names and this is my code for that ,

colmz = WorksheetFunction.Match("Sheet1", Sheets("Age").Rows(1), 0)
Nrowz = ActiveSheet.Cells(Rows.Count, colmz).End(xlUp).Row
Sheets("sheet1").Range(Cells(1, colmz), Cells(Nrowz, colmz)).Select

colm = WorksheetFunction.Match("Sheet1", Sheets("Gender").Rows(1), 0)
Sheets("sheet1").Range(Cells(1, colm), Cells(Nrowz, colm)).Select

I was able to select them individually but not together. How do I use them inside the range function and select both the columns together. Kindly give me your suggestions. Thanks in advance.

You can only select one by one so if you use 2 "select", the first one will become useless — Seb
– Seb, Commented Mar 3, 2017 at 14:37

CLR · Accepted Answer · 2017-03-03 14:48:43Z

1

You want to select the UNION of the two ranges. For clarity, I've created ranges for the two areas to .select. Using this method allows you to specify the Sheet to use also.

Dim age_range, gender_range As Range

colmz = WorksheetFunction.Match("Sheet1", Sheets("Age").Rows(1), 0)
Nrowz = ActiveSheet.Cells(Rows.Count, colmz).End(xlUp).Row
colm = WorksheetFunction.Match("Sheet1", Sheets("Gender").Rows(1), 0)

Set age_range = Sheets("sheet1").Range(Cells(1, colmz), Cells(Nrowz, colmz))
Set gender_range = Sheets("sheet1").Range(Cells(1, colm), Cells(Nrowz, colm))

Sheets("sheet1").Range(Union(age_range, gender_range).Address).Select

answered Mar 3, 2017 at 14:48

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Student of the Digital World · Accepted Answer · 2017-03-03 14:35:33Z

0

I tried the Union function and it did work for me

Union(Range(Cells(1, colmz), Cells(Nrowz, colmz)), Range(Cells(1, colm), Cells(Nrowz, colm))).Select

kindly let me know, if there is a better practice than this

answered Mar 3, 2017 at 14:35

Student of the Digital World

3,05122 gold badges74 silver badges130 bronze badges

2 Comments

Seb Over a year ago

well it depends on different things : Are you doing this only once or you will repeat this code multiple times ? If so, are the columns you want to select always called by those 2 header names or not ?

Student of the Digital World Over a year ago

@Seb I don't have to use it again and again. I only need to use them once. but still, I think I will still go with the Answer provided by CLR

Collectives™ on Stack Overflow

Selecting Multiple Columns Using Header names - Excel VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related