merging/concatenating column letter and row number

[drangel]

Hi all-

I am attempting to combine column letter with row number to create a range. Basically, I am trying to find last row and last column and assign that as my "DataRange" at the beginning of this Sub so that I can sort/subtotal based on that range. Below is the beginning of my code:


Sub BillingSutotals()
'
' BillingSutotals Macro
' The macro sorts and subtotals by the Number, Memo, and Sales Price
'


Sub Subtotal()


Dim EndRow As Integer, EndCol As String
Dim DataRange As String



Range("A1").Select
Selection.End(xlDown).Select
EndRow = ActiveCell.Row
Range("A1").Select
Selection.End(xlToRight).Select
EndCol = Split(ActiveCell(1).Address(1, 0), "$")(0)


DataRange = "A1:" & EndCol & EndRow

After reading a few articles, I tried the code below but had no success:

DataRange = "A1" & ":" & EndCol & CStr(EndRow)
DataRange = "A1" & ":" & LTrim(EndCol & Str(EndRow))


Any ideas as to how to set my DataRange with the information I currently have?

 

[Fishboy]

Hi all-

I am attempting to combine column letter with row number to create a range. Basically, I am trying to find last row and last column and assign that as my "DataRange" at the beginning of this Sub so that I can sort/subtotal based on that range. Below is the beginning of my code:


Sub BillingSutotals()
'
' BillingSutotals Macro
' The macro sorts and subtotals by the Number, Memo, and Sales Price
'


Sub Subtotal()


Dim EndRow As Integer, EndCol As String
Dim DataRange As String



Range("A1").Select
Selection.End(xlDown).Select
EndRow = ActiveCell.Row
Range("A1").Select
Selection.End(xlToRight).Select
EndCol = Split(ActiveCell(1).Address(1, 0), "$")(0)


DataRange = "A1:" & EndCol & EndRow

After reading a few articles, I tried the code below but had no success:

DataRange = "A1" & ":" & EndCol & CStr(EndRow)
DataRange = "A1" & ":" & LTrim(EndCol & Str(EndRow))


Any ideas as to how to set my DataRange with the information I currently have?

Hi drangel,

Try this instead:

Sub TEST()
' Defines DataRange as a range, not a string
Dim DataRange As Range
' Defines LastRow as last row of column A containing data
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastCol as last column of row 1 containing data
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Sets DataRange as from A1: LastRow & LastCol address
Set DataRange = Range("A1", Cells(Lastrow, LastCol))
' Selects the DataRange just as proof that it works
DataRange.Select
End Sub

 

[WarPigl3t]

Sub myMacro()
     headerRow = 1
     lastColumn = Cells(headerRow, Columns.Count).End(xlToLeft).Column
     lastRow = 0
     c = 1
     Do Until c > lastColumn

          lastRowTemp = Cells(Rows.Count, c).End(xlUp).Row 'If this line of code does not work, delete this line of code and uncomment out the code below.

          'cL = CLng(c)
          'cL = ColumnLetter(cL)
          'lastRowTemp = Range(cL & Rows.Count).End(xlUp).Row

          If lastRowTemp > lastRow Then
               lastRow = lastRowTemp
          End If
          c = c + 1
     Loop
     
     lastColumn = CLng(lastColumn)
     lastColumnLetter = ColumnNumber(lastColumn)

     'Your result is in the variable named dataRange.
     dataRange = "A" & headerRow & ":" & lastColumnLetter & lastRow

End Sub

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

 

[drangel]

Thank you Fishboy and WarPigl3t a ton! I appreciate both responses and code.

Follow-up question. One of the reason why I initially started with letter route rather than value/# on the column is because when I sort later on in the code (see below), I was going to concatenate the letters (EndCol/LastColumn) with EndRow/LastRow to sort. If I go the column number route, how/what would you suggest putting in the Range" "_ below?

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B3253" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D3253" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2:F3253" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(DataRange)
.HEADER = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply



Also, I will be doing multiple subtotals layers (as seen below) after sorting. After I sort, I can go right into subtotaling since its selected and subtotal (2 or 3 times after) without missing any of the values, right (Sort, subtotal, subtotal,...,)? I will not be missing any values in the DataRange (orginal values + additional rows after subtotaling)?

Selection.Subtotal GroupBy:=NumColno, Function:=xlSum, TotalList:=Array(CrColno), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=MemoColno, Function:=xlSum, TotalList:=Array(QtyColno, CrColno), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True