Offset The Column Of Each Selected Cell

[mrscottjr]

Hey all,

I'm hoping you might be able to assist. I'm using VBA and have gotten my code to look for a certain column header ("Carrots" in this case) and to select all the non-blank cells (cells with values "2" and "4"). I'm looking to offset the selection so that it doesn't select the "2" and "4" but rather selects the names in the first column ("Tom" and Louis"). I can't figure out how to offset the selection because I need to reference the "Carrots" column in order to know which "Names" I will need to select/copy. The values in the Carrots column don't matter to me - I just need to know how to copy only the associated names in the first column (Cells "A3" and "A5"). Is there a way to do this?

Name	Potatoes	Apples	Carrots	Candy
MARK	3	1		1
TOM		1	2
HANK	2	2		1
LOUIS			4	1

 

[jasonb75]

I'm using VBA and have gotten my code to look for a certain column header ("Carrots" in this case) and to select all the non-blank cells (cells with values "2" and "4").

You don't need to offset, just use intersect with entire row and set the column holding the names.

Intersect(Range("A:A"),Selection.EntireRow).Select

I've only used Selection and Select in order to demonstrate the syntax. In your actual code, selection is unnecessary and is unlikely to serve any real purpose. Setting the range to a variable, or working 'With' the range is preferable.

 

[mrscottjr]

Thank you! This did the trick!