VBA - Create Range for every 1000 rows and then calculate within those ranges-CodePudding

I am working with a large dataset of over 100,000 rows. My goal is to create a named range for every 1,000 rows within a column. I will then want to join all the values within those cells in each of the ranges into a separate cell. If it's possible to not have to create a separate range for each 1,000 row or to avoid naming a range then that would work too.

For example: Column B has 100,000 rows. I want to join all the values in rows 1-1000 and set the value of C1. The next 1000 rows go into C2 and the next 1000 rows going into C3.

I already have code that works to join all the cell values but my issue is doing it every 1,000 rows. Here it is:

Sub combinecell()

Dim rng As Range
Dim i As String

For Each rng In Range("B1:B1000")
           i = i & rng & "','"
           Next rng
        Range("C1").Value = Trim(i)
End Sub

I want to loop this code to avoid having to manually naming or selecting a range in order to get this done. Any suggestions?

CodePudding user response：

Try this out:

Sub combinecell()
    Const NUM As Long = 1000 ' # of rows to be read
    Dim rng As Range, c As Range, ws As Worksheet
    
    Set ws = ActiveSheet
    Set rng = ws.Range("B1").Resize(NUM)
    Set c = ws.Range("C1")
    
    Do While Application.CountA(rng) > 0 'while there's data...
        'Added space at start is to prevent the leading ' being seen as a text indicator
        'Transpose converts `rng.Value` to a 1D array for `Join()` to work on
        c.Value = " '" & Join(Application.Transpose(rng.Value), "','") & "'"
        Set rng = rng.Offset(NUM)   'step down the column of data
        Set c = c.Offset(1)         'next cell for joined string
    Loop
End Sub