Count cells, copy 400 cells, update total count of cells, continue process until count is 0

Question

Here's the process I have in mind:

1. Count all cells containing data in column A (I'm thinking "xlUp"?) and set that number as a variable.
2. Select first 400 cells in Column A (starting at A2), and fill them color Yellow.
3. Copy that data (data is pasted to another prog ((Let's call the shortcut key "!*Z")).
4. Subtract 400 from the total cell count variable.
5. Select second 400 cells in Column A (starting at A402)
6. Fill them color Yellow
7. Copy that data (!*Z).
8. Subtract 400 from updated cell count variable.
9. Repeat until the last cell of data is copied and color filled.

Any help would be great.
I feel like I've been going in circles on this for too long. It's time to ask the experts. Thank you!

Answer 1

Batch Range

• Here is my quick (short) vision of the suggested post applied to your case. Please, do share if you find an improvement.

Option Explicit

Sub BatchRange()
    
    Const sfRow As Long = 2
    Const sCol As String = "A"
    Const Batch As Long = 400
 
    ' Create a reference to the First Cell.
    Dim sfCell As Range: Set sfCell = Sheet1.Cells(sfRow, sCol)
    ' Calculate the last row.
    Dim slRow As Long
    slRow = Sheet1.Cells(Sheet1.Rows.Count, sCol).End(xlUp).Row
    
    Dim srCount As Long: srCount = slRow - sfRow + 1
    Dim bCount As Long: bCount = Int(srCount / Batch) + 1
    
    Dim srg As Range
    Dim b As Long
    Dim r As Long
    
    For b = 1 To bCount
        
        If b = bCount Then
            r = srCount Mod Batch
        Else
            r = Batch
        End If
        
        If r > 0 Then
            ' Create a reference to the current Column Range.
            Set srg = sfCell.Resize(r)
            
            
            ' Do your stuff
            ' e.g.:
            Debug.Print sfCell.Address(0, 0), srg.Address(0, 0)
            ' or:
            srg.Interior.Color = vbYellow
            
            
            ' Create a reference to the next First Cell.
            Set sfCell = sfCell.Offset(r)
        End If
    
    Next b
    
End Sub