Select All Headers with Same Name, Select All Cells Below

[KeighlR]

Good afternoon,

I have a spreadsheet with around 100 columns. One of the column headers, "case", is found multiple time. What I want to do is select all of the cells below each of these headers (down to the last cell with value). I did manage to do enough research to use VBA to select each iteration of the "case" header, but I'm not sure where to go now. I'm assuming I need to use the offset to drop down a row to the first cell with a value, and then use a range with x1Down, but nothing I do seems to work.

Any and all help is appreciated!

[Bernie Deitrick]

Try a macro like this, assuming your headers are in row 1:

Please Login or Register  to view this content.

[Sintek]

Here's a non looping option...

Please Login or Register  to view this content.

[Bernie Deitrick]

I had to re-write your select line to this - the .currentregion was selecting all columns

Please Login or Register  to view this content.

[Sintek]

Perhaps a version issue...Works for me...

[Bernie Deitrick]

Excel 2016 - If I use this within the With

Msgbox Range(Join(X, ",")).CurrentRegion.Address

I get the same as

Msgbox .Address

so the .currentregion is undoing the specifics of Range(Join(X, ","))

[jolivanes]

Re Bernie's comment Post #4.
Same here in Excel 2007

[Sintek]

Managed to test...you guys are correct...Thanks for code correction Bernie...

[Bernie Deitrick]

Perhaps a version issue...Works for me...

Managed to test...you guys are correct...

You're welcome, but you are too good to post untested code

[Sintek]

Old age...I guess...lol
Sometimes busy with so many threads and solutions at once...