Make VBA refer to first cell only in .Selection?

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
For i = 1 To Selection.Rows.Count
ActiveCell.Offset(i - 1, 0).RowHeight=foo(i)

That's bad code. It's supposed to set the selection's row heights from array foo, and it's fine ... UNLESS ... ActiveCell is not the first row in the selection.

So I'd like one of two solutions, if you don't mind helping me:
A. set Activecell to first cell in selection, preserving selection
(then code is okay as is. Is this something like activecell.row=selection.row?)
OR
B. change the code.
(Unfortunately
Selection.Offset(i - 1, 0).RowHeight=foo(i)
sets Selection.Rows.Count rows en masse! It should only do 1 at a time)

Hep me! Hep me!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,102
Office Version
  1. 365
Platform
  1. Windows
What exactly are you trying to do here?

Do you need to work with Selection?
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Exactly what I said Norie. Just like the next response, which will answer the question instead of asking another [rhetorical] one.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,102
Office Version
  1. 365
Platform
  1. Windows
Look I'm sorry but, to me at least, the post is rather unclear.

Sometimes it helps when one has an overview of the whole thing.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
How about?

Code:
For i = 1 To Selection.Rows.Count 
Selection.Cells(i, 1).Offset(i - 1, 0).RowHeight=foo(i)
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
That's precisely what I need, Andrew - thanks. One thing though - change the first "i,1" to "1,1" or lose the offset, as this skips every other row as written.

In fact that's excellent - the syntax to directly refer to any part of the selection. I'll just drop the offset entirely!

Way to go - thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,265
Messages
5,577,094
Members
412,766
Latest member
BigRusty82
Top