VBA code to dynamically find next available row.

T

telecomjohnny

New Member
Joined
Aug 30, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Rookie vba user/poster

ASK: VBA macro that combines/stacks multiple (3 column ranges) into a single 3 column stack.
The macro needs to dynamically find the next available row in order to paste-special/values of the 2nd, 3rd, 4th (3 column range sets)

example data set A

Ranges (note: all ranges are formulas/not static values)
$S2:$U15 (14 rows) refer to as range 2
$X2:$Z37 (36 rows) range 3
$AC2:$AE9 (8 rows) range 4
Total of 58 rows

Combine the data of the three 3 column ranges by, copy/paste special-Values, into a separate single 3 column range stack $N:$P 58 rows total
The problem I'm having is that the macro is NOT dynamically finding the NEW ROW LENGTHS of the (3 column ranges) that need to be stacked into $N:$P

example data set B

$S2:$U52 (51 rows) range 5 (The macro was recorded on Data set A, running the macro on this new data B only pastes 14 rows and NOT 51.)
$X2:$Z30 (15 rows) range 6
$AC2:$AE2 (15 rows) range 7
Total of 81 rows

***********copy of my macro *********
Sub Construct()
'
' Construct Macro
Sheets("Order + Phase 1").Select
Range("M2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
'copy Range 2 ESRK "$S2:$U?"
Range("S2:U2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Paste "Range 2" ESRK into Construct "Colnm N"
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copy Range 3 ESRK
Range("X2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("O1").Select
Selection.End(xlDown).Select
'****this is next step is NOT WORKING!!!! *****************************************************************************
Range("N16").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copy Range 4 ESRK
Range("AC2:AE2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("O2").Select
Selection.End(xlDown).Select
Range("N52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copy Range 5 ESRK
Range("AH2:AJ2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("O1").Select
Selection.End(xlDown).Select
Range("N60").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=14
'copy Range 6 ESRK
Range("AM2:AO2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("O1").Select
Selection.End(xlDown).Select
Range("N108").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 33
'copy Range 7 ESRK
Range("AR2:AT2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("O1").Select
Selection.End(xlDown).Select
Range("N123").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Could you describe the whole operation you need to perform, because I seem that things could be much simpler than now.
The stacked array in which columns need to be copied? You are in Office 365, can you check if the following functions are available:
- UNIQUE VSTACK
 
Upvote 0
let me put this another way, I have many/hundreds of columns arranged with the same 3-group of headers FCCID, ESRK, ESRN. I need to stack the data into a single 3-column list.

Before
FCC IDConstructESRNFCC IDESRKESRNFCC IDESRKESRNFCC IDESRKESRN
1997​
51571210559115151051
2023​
5157129092
9115151051​
2027​
5157121018
9115151051​
2023​
5157129093
9115151051​
2027​
5157121018
9115151051​
2023​
5157129094
9115151051​

What I NEED it to look like below, with-out having to copy paste. I have way to much data to perform this manually.
FCC IDESRKESRN
1997​
51571210559115151051
2023​
51571290929115151051
2023​
51571290939115151051
2023​
51571290949115151051
2027​
51571210189115151051
2027​
51571210189115151051
 
Upvote 0
telecomjohnny said:
let me put this another way, I have many/hundreds of columns arranged with the same 3-group of headers FCCID, ESRK, ESRN. I need to stack the data into a single 3-column list.

Before
FCC IDConstructESRNFCC IDESRKESRNFCC IDESRKESRNFCC IDESRKESRN
1997​
51571210559115151051
2023​
5157129092
9115151051​
2027​
5157121018
9115151051​
2023​
5157129093
9115151051​
2027​
5157121018
9115151051​
2023​
5157129094
9115151051​

What I NEED it to look like below, with-out having to copy paste. I have way to much data to perform this manually.
FCC IDESRKESRN
1997​
51571210559115151051
2023​
51571290929115151051
2023​
51571290939115151051
2023​
51571290949115151051
2027​
51571210189115151051
2027​
51571210189115151051
Click to expand...
the rows of each FCCID+ESRK+ESRN header group very from 1 row to hundreds of rows. I need to consolidate every FCCID+ESRK+ESRN header groups date on top of each other under the like headers with not blank cells.
 
Upvote 0
You didn't respond about the availability of new functions that could simplify the job (namely, the VSTACK function), so lets go with a "conventional" macro.
Copy this code into a vba standard module:
Code: 
Sub StackBlocks()
'https://www.mrexcel.com/board/threads/vba-code-to-dynamically-find-next-available-row.1215532/#post-5941204
Dim FCC As Range, firstAddress As String, LastF As Long, StkSh As Worksheet
'
Sheets("Sourcez").Select                         '<<< The sheet with the data to be stacked
Set StkSh = Worksheets("Stacked")               '<<< The sheet where to stack the result
'
If Application.WorksheetFunction.CountA(StkSh.Range("A1:C1")) <> 3 Then
    StkSh.Range("A1:C1").Value = Array("FCC ID", "ESRK", "ESRN")
End If
    With ActiveSheet.UsedRange
        Set FCC = .Find("FCC ID", LookIn:=xlValues)
        If Not FCC Is Nothing Then
            firstAddress = FCC.Address
            Do
                LastF = FCC.Offset(10000, 0).End(xlUp).Row
                If LastF > FCC.Row And (FCC.Offset(0, 1) = "ESRK") And (FCC.Offset(0, 2) = "ESRN") Then
                    Call FCCCopy(FCC, LastF, StkSh)
                End If
'                Set FCC = .FindNext(FCC)
                Set FCC = .Find("FCC ID", LookIn:=xlValues, After:=FCC)
                If FCC Is Nothing Or FCC.Address = firstAddress Then Exit Do
            Loop
        End If
    End With
MsgBox ("Completed")
End Sub


Sub FCCCopy(ByRef myran As Range, ByVal LRow As Long, ByRef deSh As Worksheet)
Dim mNext As Long, aH As Long
'
mNext = deSh.Range("A:C").Find(What:="*", After:=deSh.Range("A1"), _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
aH = LRow - myran.Row
deSh.Cells(mNext, 1).Resize(aH, 3).Value = myran.Offset(1, 0).Resize(aH, 3).Value
End Sub
This will search the blocks on a "source" worksheet and stack them on a "destination" worksheet. Source and Destination sheet names need to be specified in the lines marked <<< at the beginning of the code.
When you are ready, start Sub StackBlocks

It uses Find to search for the several blocks of FCC ID

Try...
 
Upvote 0
Solution
Anthony47 said:
You didn't respond about the availability of new functions that could simplify the job (namely, the VSTACK function), so lets go with a "conventional" macro.
Copy this code into a vba standard module:
Code: 
Sub StackBlocks()
'https://www.mrexcel.com/board/threads/vba-code-to-dynamically-find-next-available-row.1215532/#post-5941204
Dim FCC As Range, firstAddress As String, LastF As Long, StkSh As Worksheet
'
Sheets("Sourcez").Select                         '<<< The sheet with the data to be stacked
Set StkSh = Worksheets("Stacked")               '<<< The sheet where to stack the result
'
If Application.WorksheetFunction.CountA(StkSh.Range("A1:C1")) <> 3 Then
    StkSh.Range("A1:C1").Value = Array("FCC ID", "ESRK", "ESRN")
End If
    With ActiveSheet.UsedRange
        Set FCC = .Find("FCC ID", LookIn:=xlValues)
        If Not FCC Is Nothing Then
            firstAddress = FCC.Address
            Do
                LastF = FCC.Offset(10000, 0).End(xlUp).Row
                If LastF > FCC.Row And (FCC.Offset(0, 1) = "ESRK") And (FCC.Offset(0, 2) = "ESRN") Then
                    Call FCCCopy(FCC, LastF, StkSh)
                End If
'                Set FCC = .FindNext(FCC)
                Set FCC = .Find("FCC ID", LookIn:=xlValues, After:=FCC)
                If FCC Is Nothing Or FCC.Address = firstAddress Then Exit Do
            Loop
        End If
    End With
MsgBox ("Completed")
End Sub


Sub FCCCopy(ByRef myran As Range, ByVal LRow As Long, ByRef deSh As Worksheet)
Dim mNext As Long, aH As Long
'
mNext = deSh.Range("A:C").Find(What:="*", After:=deSh.Range("A1"), _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
aH = LRow - myran.Row
deSh.Cells(mNext, 1).Resize(aH, 3).Value = myran.Offset(1, 0).Resize(aH, 3).Value
End Sub
This will search the blocks on a "source" worksheet and stack them on a "destination" worksheet. Source and Destination sheet names need to be specified in the lines marked <<< at the beginning of the code.
When you are ready, start Sub StackBlocks

It uses Find to search for the several blocks of FCC ID

Try...
Click to expand...
Anthony, I was unable to locate/make the VSTACK function work however the macro code worked the first time I tried! This resolves my issue as is.

If you would please give me a copy of the SAME MACRO code but insert another column with header "A/D?" (quotes are not included in the header) at A1.
A/D?FCC IDESRKESRN

Once I have this added column I can identify all the code that needs to change in order to add/subtract columns for other projects.
This is a huge help Thank you!
 
Upvote 0
would please give me a copy of the SAME MACRO code but insert another column with header "A/D?"
Click to expand...
To cope with this request we need to modify these lines
Code: 
                If LastF > FCC.Row And (FCC.Offset(0, 1) = "ESRK") And (FCC.Offset(0, 2) = "ESRN") Then
                    Call FCCCopy(FCC, LastF, StkSh)
                End If

Code: 
deSh.Cells(mNext, 1).Resize(aH, 3).Value = myran.Offset(1, 0).Resize(aH, 3).Value

In both cases it is necessary to know the position of this extra column
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
160
shknbk2
S
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
171
DanteAmor
DanteAmor
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
516
jolivanes
jolivanes
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
337
Joe4
Joe4
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
311
kevin9999
K

Forum statistics

Threads
1,214,655
Messages
6,120,760
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top