Results 1 to 3 of 3

VBA return column letters to select column range

This is a discussion on VBA return column letters to select column range within the Excel Questions forums, part of the Question Forums category; I want to select a range of columns from a workbook I have no formatting contorl over and copy them ...

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    156

    Default VBA return column letters to select column range

    I want to select a range of columns from a workbook I have no formatting contorl over and copy them to another workbook sheet for analysis.

    The code below is a test code and so far it selects the highest and lowest numbers representing a date of a month (i.e. 1, 2, 3, ... 30) depending how many days there are each month. Then it correlates the cell addresses and column numbers based upon those minimum and maximum values so I caputure the whole month. Now I want to correlate column numbers, then select those columns and the range of columns in between to copy the data.

    I was able to return the column numbers for the min and max values and thought I could do

    Columns(scol:lcol).copy

    and it would work, but it doesn't. I've tried a number of tactics and help menu suggestions and no success. Maybe I have to return column letters, but I haven't found logical code for that.

    This is my test code. It

    Code:
    Sub FindSmLgnmAdr()
    Dim lgnm As Integer
    Dim lrng As Range
    Dim ladr As String
    Dim lcol As String
    Dim smnm As Integer
    Dim srng As Range
    Dim sadr As String
    Dim scol As Integer
    Dim colnm As Integer
    'Unmerge cells for copying
    Range("E1:G2").Select
    With Selection
        .MergeCells = False
    End With
    'Finds number of days in month and copies data range
    lgnm = WorksheetFunction.Max(Range("3:3").Value)
    smnm = WorksheetFunction.Min(Range("3:3").Value)
    Set lrng = Range("3:3").Find(lgnm)
    Set srng = Range("3:3").Find(smnm)
    sadr = srng.Address
    scol = Range(srng.Address).Column
    ladr = lrng.Address
    lcol = Range(lrng.Address).Column
     
    Range("E1:G2").Select
    With Selection
        .MergeCells = True
    End With
    MsgBox smnm & " = " & sadr & " = " & scol & vbNewLine & _
        lgnm & " = " & ladr & " = " & lcol
     
    End Sub

  2. #2
    Board Regular
    Join Date
    Nov 2011
    Posts
    156

    Default Re: VBA return column letters to select column range

    I figured out my own problem. I had to format it this way:

    Range(Columns(scol), Columns(lcol)).Select

    Sorry if I waisted anyone's time. Thanks!

    So here is the corrected code:
    Code:
    Sub FindSmLgnmAdr()
    Dim lgnm As Integer
    Dim lrng As Range
    Dim ladr As String
    Dim lcol As Integer
    Dim smnm As Integer
    Dim srng As Range
    Dim sadr As String
    Dim scol As Integer
    Dim colnm As Integer
    'Unmerge cells for copying
    Range("E1:G2").Select
    With Selection
        .MergeCells = False
    End With
    'Finds number of days in month and copies data range
    lgnm = WorksheetFunction.Max(Range("3:3").Value)
    smnm = WorksheetFunction.Min(Range("3:3").Value)
    Set lrng = Range("3:3").Find(lgnm)
    Set srng = Range("3:3").Find(smnm)
    sadr = srng.Address
    scol = Range(srng.Address).Column
    ladr = lrng.Address
    lcol = Range(lrng.Address).Column
    Range(Columns(scol), Columns(lcol)).Select
    Range("E1:G2").Select
    With Selection
        .MergeCells = True
    End With
    MsgBox smnm & " = " & sadr & " = " & scol & vbNewLine & _
        lgnm & " = " & ladr & " = " & lcol
        
    End Sub

  3. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,983

    Default Re: VBA return column letters to select column range

    Why are you selecting a range, then selecting a different range right after it?

    BTW, It's very rare that you actually need to Select anything in VBA. You can work with ranges directly.

    Intead of this, for example:
    Code:
     
     
    Range("E1:G2").Select
    With Selection
        .MergeCells = True
    End With
    This will accomplish the same thing:

    Code:
     
    Range("E1:G2").MergeCells=True
    Office 2007/2010

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com