VBA - Can't Select Correct Columns

C45P4R

New Member
Joined
Oct 15, 2014
Messages
39
I just copied and pasted some old code and changed the references around so it did the same stuff on a different tab. For some reason, copy and paste has broken the code and it works until the new part.

It is currently not selecting the columns I specify, and is applying my formatting to everything. I realise .select is inefficient but I don't know enough to do it any other way.

It starts going wrong here, but only with the cell selections. It will run, but everything is centered and formatted as a date...

Code:
'Data Formatting: Align these columns

    Range("H:I,T:W,AB:AB").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Range("A3").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
'Data formatting: Set these columns to the desired formats.

    Range("J:K,S:S,X:AA").Select
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
    Range("AC:AE").Select
    Selection.NumberFormat = "mm/dd/yyyy"
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

revcanon

Board Regular
Joined
Mar 26, 2015
Messages
173
Code:
'Data Formatting: Align these columns

    Range("H:I,T:W,AB:AB").HorizontalAlignment = xlCenter
    Range("A3").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .ReadingOrder = xlContext
    End With
    
'Data formatting: Set these columns to the desired formats.

    Range("J:K,S:S,X:AA").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
    Range("AC:AE").NumberFormat = "mm/dd/yyyy"

I shortened the code a bit, but I didn't have any problem with the way it is selecting or formatting.

In order to know why it is not selecting the proper columns, I may need to see what code you have prior to this area if this isn't working for you.
 

C45P4R

New Member
Joined
Oct 15, 2014
Messages
39
There is quite a lot of code above this but it is basically the same code. All of this works further up in the code just not here...

This is the code that comes directly before what i last posted.

Code:
'Switch to the new data
    
    Dim celltext As String
        Workbooks(1).Activate
        celltext = ActiveSheet.Range("A2").Text
            If InStr(1, celltext, "Document X") Then
            Else: Workbooks(2).Activate
            End If
        celltext = ActiveSheet.Range("A2").Text
            If InStr(1, celltext, "Document X") Then
            Else: Workbooks(3).Activate
            End If

'Select all data and cut it. Close this (now empty) workbook, and paste the data into the "Data 2" tab.
    
    Range("A8:AE8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Cut
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    ThisWorkbook.Activate
    Worksheets("Data 2").Select
    Range("A4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = True

'Set the print range for the current data set.

    Range("A1:AE1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.PageSetup.PrintArea = SelectedRange

'Data formatting: wrap text and fix formatting that prevents cells from being summed.

    Range("A4:AE4").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .WrapText = True
        
    Selection.Replace What:="$?", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
 

Watch MrExcel Video

Forum statistics

Threads
1,123,384
Messages
5,601,326
Members
414,443
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top