Better than .Select in VBA

[Andrzejek]

This is actually code in VB 6 where I create a lot of reports in Excel.

Usually, to dump data into Excel I use:

Dim xlApp As Excel.Application

With xlApp
  ....
  .Range("A4:D4).Value = Array("X","Y",123,"AbC")
  ....

which works very well.

But many times I use .Select which may not be very efficient because of .Select (right???) :

  .Range("C5")[blue].Select[/blue]
  .Selection.Font.Bold = True
  .ActiveCell.FormulaR1C1 = "Some Info"

or to format Cells or Columns:

  .Cells[blue].Select[/blue]
  .Selection.NumberFormat = "@"

  .Columns("G:M")[blue].Select[/blue]
  .Selection.HorizontalAlignment = xlCenter
  .Columns("A:M").EntireColumn.AutoFit
  .Range("A1").Select

How can I improve this code and avoid use of .Select?


Have fun.

---- Andy

 

[PHV]

You may try something like this:
1) .Range("C5").Font.Bold = True

2) .Cells.NumberFormat = "@"

3) .Columns("G:M").HorizontalAlignment = xlCenter

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherHiggins]

Correct - Select and Activate can slow things down.

  .Range("C5").Select
  .Selection.Font.Bold = True

can be shortened to

  .Range("C5").Font.Bold = True

Then you can use With to make things a little easier

    With Range("C5")
        .Font.Bold = True
        .FormulaR1C1 = "Some Info"
    End With

    With Cells
        .NumberFormat = "@"

        .Columns("G:M").HorizontalAlignment = xlCenter
        .Columns("A:M").EntireColumn.AutoFit
        .Range("A1").Select
    End With

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

Help us help you. Please read FAQ 181-2886 before posting.