Issue with selecting columns via macros

**Jo2710** · 11-10-2014, 05:34 PM

Hi everyone,

I'm working on a macro that does a fair amount of formatting & removing of columns. I've got an issue when selecting columns, instead of just selecting one column a couple are being selected but I don't understand why. Any help would be greatly appreciated.

Here's the first instance where it goes wrong, the first bit of code works fine but I've put it in so you can see that I've selected the worksheet. Instead of changing the one column to text it changes a couple of columns.

 'Copy from CSV to Quote and delete first row
    Sheets("CSV").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
    Sheets("Quote").Select
    Range("B12").Select
    ActiveSheet.Paste
    Range("B12").Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
        
    'Change to text
    Columns(3).Select
    Selection.NumberFormat = "@"

Thanks
Jo

**Norie** · 11-10-2014, 05:41 PM

Instead of using Select/Selection try explicitly referring to the column(s).

Which column(s) do you want to format?

 'Copy from CSV to Quote and delete first row
    Sheets("CSV").CurrentRegion Sheets("Quote").Range("B12")

    With Sheets("Quote")
        .Rows(12).Delete
        .Columns(3).NumberFormat = "@"
    End With

**Jim885** · 11-10-2014, 05:50 PM

Obviously, this is a recorded macro.
I can reduce your code, but I'm still not sure what you are trying to accomplish.
You have this line of code ->> Selection.EntireRow.Delete
What row are you deleting? Because as the code is written, it will delete the same row where the data was just pasted.

Here's a slight mod to your code (it can be reduced further), but we'd like to know wht you are trying to do so you get what you need.

Sub aa()
Sheets("CSV").Range("A1").Copy
    Sheets("Quote").Select
    Range("B12").PasteSpecial
    Application.CutCopyMode = False
    Selection.EntireRow.Delete '<-- what are you tring to delete?
    Columns(3).NumberFormat = "@"
End Sub

**Jo2710** · 11-12-2014, 08:46 PM

Thanks Jim & Norie for your quick replies! I really appreciate it.

From your comments I managed to trim my code right down and get it to work. The "select" function seemed to be causing a lot of grief but the last time I used this piece of code I had no problems.

What I was trying to do was copy a chunk of data from one sheet to another then remove the header, this data would vary in length but would always be contiguous.
Then I wanted to change the format of one of the columns.

   'Copy from CSV to Quote and delete first row
    Sheets("CSV").UsedRange.Copy Destination:=Sheets("Quote").Range("B12")
    Sheets("Quote").Range("B12").EntireRow.Delete
         
    'Change to text
    Sheets("Quote").Columns(2).NumberFormat = "@"

**Jim885** · 11-12-2014, 10:10 PM

OK.
Glad you got it solved.

Issue with selecting columns via macros

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Issue with selecting columns via macros

Re: Issue with selecting columns via macros

Re: Issue with selecting columns via macros

Re: Issue with selecting columns via macros

Re: Issue with selecting columns via macros

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Selecting all data in specific columns without selecting adjacent column.

Selecting all data in specific columns without selecting adjacent column.

[SOLVED] Issue w/ Current Region, Select 3 Columns of Data without selecting adjacent columns

Issue Selecting a Dropdown from IE using VBA

Cell Selecting issue

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions