How do I select a range of cells based on named start and end cells in VBA?

Question

I'm pulling data from a MySQL db into a worksheet by saving it as a csv and pasting that csv data into a worksheet. This csv holds all the data for all clients, separated by marker cells that look like: "Client1:START" and "Client1:END". Then I'm running a macro that copies two pages: one with individual clients' data from the aggregated sheet, and another that has charts that run off that data.

I've got almost everything working: copying all the pages and updating the references from the charts and the cells that pull information from the data sheet for that client.

The only thing left to do is copy the data from the aggregated sheet to each individual client's sheet. I gave this a lot of thought, and it seems that the easiest way to do this, given that I don't know the number of rows that will be generated for a given client (anything between zero and 31, since this is a monthly report), is to have "Client1:START" in the first cell of the row preceding the first row for that client, and "Client1:END" in the first cell of the row after.

Then I can simply search through the cells until I find those two, name them (since I can't figure out how to save a cell address in a variable yet), then offset them somehow to get the actual range that I want, minus the markers.

Then I can copy that range and paste it into the newly-created data sheet.

I haven't even gotten as far as the offsetting yet, actually. I'm still floundering trying to select the cells based on their names. This is what I have:

Dim Client
Dim SelectedCell
Dim StartCell
Dim EndCell

For Each Client In Array("Client1", "Client2")

    StartCell = Client & "StartCell"
    EndCell = Client & "EndCell"

    Sheets("ALL-DATA").Select
    For Each SelectedCell In Range("A1:D20")
        If SelectedCell.Value = Client & ":START" Then
            SelectedCell.Name = StartCell
        End If
        If SelectedCell.Value = Client & ":END" Then
            SelectedCell.Name = EndCell
        End If
    Next SelectedCell

    Range(StartCell & ":" & EndCell).Select  '<-- This won't compile
Next Client

That Range won't let me select using variables, so I'm a bit stuck. It appears that VBA only lets you select a range using a string of addresses.

If anybody can point me in the right direction, it would be much appreciated. And if you know how I can adjust the selection to exclude the actual markers (taking into account the possibility of the data being zero rows long) that would be fantastic and a huge bonus.

Thanks!

Answer 1

I handle this in the number of functions.

1) Get the references to the start and end of the table:

Use Application.Range or Me.Range To find the start and end of the table that's identified by a named range. In this case a the table has a header and a foot row to mark the beginning and end of the table.

I keep these functions inside the worksheet's module, which allows me to use Me.Range. I also use limit the scope of the named range to the worksheet only.

Private Function GetTableStart() As Long
    GetTableStart = Me.Range("TABLE_START").Row + 1
End Function

Private Function GetTableEnd() As Long
    GetTableEnd = Me.Range("TABLE_END").Row - 1
End Function

I also name the columns, COLUMN_ID is a named range that selects a whole column in a worksheet. e.g It's named range is 'Worksheet'!$A:$A

Private Function GetColumnId() As Long
    GetColumnId = Me.Range("COLUMN_ID").Column
End Function

Private Function GetLastColumn() As Long
    GetLastColumn = Me.Range("COLUMN_LAST").Column
End Function

2) Change the Table's Size. Give it the number of rows you want and it'll resize the table for you. :

Private Sub FixTableSize(expectedRows As Long)

    If expectedRows = 0 Then
        Err.Raise vbObjectError + 513, Me.name, "Cannot resize the table's number of rows to 0"
    End If

    Dim startRow As Long
    Dim endRow As Long
    Dim startColumn As Long
    Dim endColumn As Long
    Dim numberOfRows As Long
    Dim table As Range

    startRow = GetTableStart()
    endRow = GetTableEnd()
    startColumn = GetColumnId()
    endColumn = GetColumnEnd()

    numberOfRows = endRow - startRow + 1
    Set table = Me.Range(Me.Cells(startRow, startColumn), Me.Cells(endRow, endColumn))

    If numberOfRows > 0 Then
        ' Prevent it from clearing the headers
        table.ClearContents
    End If

    With Me
        Dim cnt As Integer
        If expectedRows > numberOfRows Then
            For cnt = 1 To (expectedRows - numberOfRows)
                table.Rows(2).Insert xlShiftDown
            Next cnt
        ElseIf expectedRows < numberOfRows Then
            For cnt = 1 To (numberOfRows - expectedRows)
                table.Rows(1).Delete xlShiftUp
            Next cnt
        End If
    End With

End Sub

3) Populate the table. Once the table is the right size, I populate the table with the data I want.

Private Sub PopulateIssues(sprints() As JIRASprint)
    Dim currentSprint As Variant
    Dim currentRow As Long

    currentRow = GetTableStart()
    For Each currentSprint In sprints
        Me.Cells(currentRow, GetColumnId()).Value = currentSprint.Id
        Me.Cells(currentRow, GetColumnName()).Value = currentSprint.name
        Me.Cells(currentRow, GetColumnClosed()).Value = currentSprint.Closed
        Me.Cells(currentRow, GetColumnStartDate()).Value = currentSprint.startDate
        Me.Cells(currentRow, GetColumnEnd()).Value = currentSprint.endDate

        If currentSprint.completeDate <> 0 Then
            Me.Cells(currentRow, GetColumnCompleteDate()).Value = currentSprint.completeDate
        End If

        currentRow = currentRow + 1
    Next

End Sub

4) Then I put it all together with one subroutine called update tables.

Private Sub UpdateTable()
On Error GoTo ErrHandler

    Dim numberOfRows As Long
    Dim sprints() As JIRASprint ' Where JIRASprint is a custom Class I made.

    numberOfRows = ... ' Find out how many rows I need somehow.
    Set sprints = GetData() '  Get the data however you want.


    ' turn these off so it updates faster ...
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    FixTableSize numberOfRows
    PopulateIssues sprints

    ' turn them back on ...
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

Exit Sub

ErrHandler:
    ' turn them back on ...
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox "An error occured while updating the worksheet"

End Sub

Hope this helps!

Answer 2

This code doesn't compile for a number of reasons I mentioned in comments above. I believe this below will work. You should be in the habit of always declaring your variables and using Option Explicit to prevent typos/etc.

• You need a way to get the cell's Address, and that is by referencing it's .Address property :) Cells and ranges don't have a .Name property, so your code would actually fail on the line SelectedCell.Name = StartCell
• Your assignment statements are backwards. IN order to assing to the StartCell variable, that variable must be on the left of the assignment statement, and if it needs to represent an object like a cell/range, then you must also use the Set keyword, i.e., Set StartCell = Range("A1").

I have also updated this to avoid any use of Select method. it's 99.9% of the time unnecessary to Select or Activate anything in Excel.

Dim Client as Variant
Dim SelectedCell as Range
Dim StartCell as Range
Dim EndCell as Range
Dim ClientRange as Range

For Each Client In Array("Client1", "Client2")

    For Each SelectedCell In Sheets("ALL-DATA").Select.Range("A1:D20")
        If SelectedCell.Value = Client & ":START" Then
            Set StartCell = SelectedCell
        ElseIf SelectedCell.Value = Client & ":END" Then
            Set EndCell = SelectedCell
        End If
    Next SelectedCell

    Set ClientRange = Sheets("ALL-DATA").Range(StartCell.Address & ":" & EndCell.Address)
Next Client

Now you've qualified ClientRange as belonging to "All-DATA" worksheet, there is generally no need to ever Select or Activate it for any reason. Doing so only adds unnecessary operations and complexity to the code and slows its performance.

Answer 3

If the rows contain client specific cell then use auto filter to show only those rows. Then do a Select All (record it).

To your specific question.

To find your cell I recorded (Tools - Macro - Record New Macro) Edit -Find

Cells.Find(What:="fred", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

And you can change that a bit to

Set client = Cells.Find(What:="fred", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)