VBA Excel Setting Range

[dprayner]

Hi people.

I am trying to pass a variable (ie. num = Count()) for number of rows to a Range or Columns function and have the specific column (ie. A) centered. I know you can use Columns(A:A).Select, but I want to speed it up and only center the rows that have data. I am actually looking to center all the data from Columns A - H and the # of rows changes each day. Any suggestions. THank you DAVE

 

[ETID]

sub select_cells()

Dim x As Integer
Dim off_set As Integer
Dim col_num As Integer

x = 1
off_set = 12
col_num = 1

Range(Cells(x, col_num), Cells(x + off_set, col_num)).Select

end sub

 

[PHV]

Why not simply this oneliner ?
Columns("A:H").HorizontalAlignment = xlCenter

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Dave,

If this is a worksheet that is repopulated with data, why not simply set columns A-H to be centered manually and be done with it? Or, if you are writing data to a new worksheet, set the centered property in one go using something like:

Worksheets("MySheet").Columns("A:H").HorizontalAlignment = xlCenter

Am I missing something?


Regards,
Mike

 

[ETID]

' Ooops, missed the centering part of your request.

sub Center_cells()

Dim x As Integer
Dim off_set As Integer
Dim col_num As Integer

x = 1
off_set = 12
col_num = 1

Range(Cells(x, col_num), Cells(x + off_set, col_num)).HorizontalAlignment = xlCenter

End Sub

 

[AnotherHiggins]

I know you can use Columns(A:A).Select, but I want to speed it up and only center the rows that have data.

Avoiding “Select” will speed up code, but defining how many rows are populated, then setting that range to be centered will, if there's a difference at all, only take longer than simply using
Columns("A:H").HorizontalAlignment = xlCenter
as PHV and rmikesmith suggested.

Heck, even using
Cells..HorizontalAlignment = xlCenter
is just about instantaneous.

Here are some ideas on how to speed up code.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dprayner]

Thank you everyone. Your the BEST! DAVE