[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

Naming a range in excel

I have the code below which works great. My last thing I want to do now is have the data that was just entered be given a named range. I tried

ActiveSheet.Range("A1", fldCount,rowCount)).Name = "Data"

but that doesn't work. How do I reference the columns by the number?

    Dim rsReports As ADODB.Recordset
   
    Set rsReports = rs_TheCarson("9999999999", "0000000000")
   
    If rsReports.RecordCount > 0 Then
       
        Launch_Progress (rsReports.RecordCount)

        Open_Excel
       
        Dim iCol As Integer
        Dim iRow As Integer
       
        Dim fldCount As Integer
        Dim rowCount As Integer
               
        fldCount = rsReports.Fields.Count
        rowCount = (rsReports.RecordCount - 1)
       
        For iCol = 1 To fldCount
       
            xlWs.Cells(1, iCol).Value = rsReports.Fields(iCol - 1).Name
           
            Next
           
        xlWs.Cells(1, (fldCount + 1)).Value = "Sum Of Points"
           
        rsReports.MoveFirst
       
        Dim temp As String
       
        For iRow = 0 To rowCount
       
            For iCol = 1 To fldCount
               
                If (rsReports.Fields(iCol - 1).Value <> "") Then
               
                    temp = rsReports.Fields(iCol - 1).Value
               
                    xlWs.Cells((iRow + 2), iCol).Value = UCase(RTrim(temp))
                   
                End If
               
            Next
                         
            Progress_Update (iRow)
               
            rsReports.MoveNext
           
        Next
       
       
        Progress_Complete
       
        close_excel
       
    Else
       
        MsgBox "There is no data to report.", vbInformation, "No Data"
       
    End If
   
    Exit Sub
0
arajoe
Asked:
arajoe
  • 3
  • 2
1 Solution
 
EDDYKTCommented:
>>How do I reference the columns by the number?

ActiveSheet.Columns(1).Name = "Data"
0
 
arajoeAuthor Commented:
But that only make the first column have that name. I need the range of A1 to the column of whatever the fldcount and the row of rowcount.
0
 
EDDYKTCommented:
ActiveSheet.Range(Cells(1, 1), Cells(rowCount, fldcount)).Name = "Data"
0
 
EDDYKTCommented:
or

ActiveSheet.Range("A1", Cells(rowCount, fldcount)).Name = "Data"
0
 
arajoeAuthor Commented:
Awesome, worked great.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now