using macro recorder to change a whole column's format

[still learning]

Hi
Just wondering……..
When I open a worksheet, the advance filter looks for criteria from another sheet in the same workbook.
One of the columns (B:B) seems to put the extracted data in with the format to the “left”.
The data is always a text
What I did was ran the macro recorder and have the whole column formatted to “center”.
(the cursor is at A1 when this part of the macro starts and the cursor goes back to A1 to continue the macro)

My question is :
Why does the macro recorder make the column as A:A instead of B:B

ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    With Selection
        .HorizontalAlignment = xlCenter
    ActiveCell.Offset(0, 1).Select
    End With

mike

 

[Joe4]

I believe it is really saying the first column in your selection (that "A:A" really means the first column in your selection).

Your selection is:

ActiveCell.Offset(0, 1)

If you start in cell A1, and offset one column, you are now in B1.
So, the first column in that selected range is column B.

 

[still learning]

Hi Joe4,
I see.
is there a way to write that part of the macro where if I'm in A1, I don't have to move to the heading of B:B and then move back.
Just wondering

mike

 

[Joe4]

I am not 100% clear on what it is you are trying to accomplish.
Are you starting in cell A1 and wanting to select column A, or column B?

 

[still learning]

When I open the sheet.
the cursor moves to A1, in case it's not there already.
Then the advance query ( Filter>>Advance )finds the data that i want from a different sheet in the same workbook and puts it in the proper column.
for reasons unknown,( to me, anyway) the text that goes into B:B: is formatted to "left", even though its formatted "center" on the other worksheet.
I then go to B:B and format it to "center"
Then the cursor goes back to A1
Then the cursor drops down to A4 and freezes that line (for the header)
then it drops down to the first empty column in A
Using the macro recorder, it called B:B, A:A
I was wondering why.
You explained it very well.
Then I was wondering if I could have the macro format B:B with out going to it first.
Here is the full macro.
The criteria is always the same>>>becky.

Sub becky()
Sheets("Becky 2025").Select
Application.ScreenUpdating = False
Application.Goto Reference:="R1C1"
         Range("beckydata").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "beckycriteria"), CopyToRange:=Range("beckyextract"), Unique:=False
 ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    With Selection
        .HorizontalAlignment = xlCenter  
    End With
 Range("A1").Select
 Range("A4").Select
 ActiveWindow.FreezePanes = True
Selection.End(xlDown).Select
             '  I'll use this when I have more than 8-10 lines
             ' it'll  make it easier for me to see
     'ActiveWindow.ScrollRow = ActiveCell.Row - 8
ActiveCell.Offset(1, 0).Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[MARK858]

for reasons unknown,( to me, anyway) the text that goes into B:B: is formatted to "left", even though its formatted "center" on the other worksheet.

It doesn't matter what it was formatted to align to on the other sheet, the column on the sheet will be be formatted to General and if you are putting text in the cells it will align to the left.

ActiveCell.Offset(0, 1).Columns("A:A")

This is telling the cell to offset by one cell to the right i.e. from A1 to B1 then use the first column of that cell (.Columns(1)) i.e. column B

Then I was wondering if I could have the macro format B:B with out going to it first.

Replace

ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    With Selection
        .HorizontalAlignment = xlCenter  
    End With

with

Range("B:B").HorizontalAlignment = xlCenter

 

[Joe4]

Thanks for the assist, Mark. I was pulled away for a little while.

 

[still learning]

Hi Mark858
Hi Joe4
Thank you both for the very informative response.
I know my way works, but , as my screen name shows, I'm always learning.
I have to say that because of this forum, I've been able to write code.
everything I've learned is from here.
I'm also glad that I've never experienced a reason to feel ashamed because of my questions.

mike
As a PS, is there a way to show more than one solution?

 

[Joe4]

As a PS, is there a way to show more than one solution?

No, unfortunately the software used does not allow for that, but you can "Like" as many posts as you want in the thread.