VBA return column letters to select column range

[ez08mba]

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

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

 

[ez08mba]

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:

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
[COLOR=red][B]Range(Columns(scol), Columns(lcol)).Select[/B][/COLOR]
Range("E1:G2").Select
With Selection
    .MergeCells = True
End With
MsgBox smnm & " = " & sadr & " = " & scol & vbNewLine & _
    lgnm & " = " & ladr & " = " & lcol
    
End Sub

 

[Scott Huish]

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:

Range("E1:G2").Select
With Selection
    .MergeCells = True
End With

This will accomplish the same thing:

Range("E1:G2").MergeCells=True