Dynamic Address by VBA

[SeliM]

Good morning

Thank you for reading my enquiry

I have script that starts from a static position and copies a list of companies who have submitted quotes et. Viz:

'To copy current list of submitters for populating the charts and word master
    Sheets("Data").Visible = True
    Sheets("Data").Select
    Range("A11:A11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Setup").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

All worked fine until the third party system started to vary output to varies rows, for example "A9".

I cannot work through script that asks for the system to find the dynamic starting point for the company list copy, say to start from $A$9

Any guidance most welcome.

Mel

 

[Cubist]

Can you post a sample of the data? How do you determine where the data starts?

 

[MikeVol]

Hi @SeliM.
We can allow the user to specify the starting line to select the desired range on the worksheet. Here is an option:

Option Explicit

Sub Test()
   
    ' Allow user to specify initial working string
    Dim startRow As Long
    startRow = Application.InputBox("Enter the starting row for selection in column A:", Type:=1)
    If startRow < 1 Then Exit Sub
    Application.ScreenUpdating = False

    With Worksheets("Data")
        .Visible = True
        .Range("A" & startRow, .Range("A" & startRow).End(xlDown)).Copy
    End With

    With Worksheets("Setup").Range("B4")
        .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Perhaps you're not looking for that, but this option works. Yes, I know, this will create extra work for you, but.

Or use another method, Range.Find finds the first filled cell in column A and then selects the range.

Option Explicit

Sub Test_v2()
    Application.ScreenUpdating = False

    With Worksheets("Data")
        .Visible = True

        Dim firstCell As Range
        Set firstCell = .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole)

        If Not firstCell Is Nothing Then
            .Range(firstCell, .Cells(.Rows.Count, "A").End(xlUp)).Copy

            With Worksheets("Setup").Range("B4")
                .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
            End With

            Application.CutCopyMode = False
        Else
            MsgBox "No data found in column A of the Data sheet.", vbExclamation
        End If

    End With

    Application.ScreenUpdating = True
End Sub

It's up to you. Glad to help. Good luck.

 

[MARK858]

Or use another method, Range.Find finds the first filled cell in column A and then selects the range.

Unless the first filled cell is A1 (Edit: or whatever is the first cell in any range)

With the data below

Test Workbook .xlsb
	A
1	Order ID
2	102-21-N86
3	106-23-N78
4	110-6-N01
5	106-15-N34
6	107-29-F22
7	104-8-F08
8
9
10
Data

Compare the results of the 2 codes below

Sub Test_v3()
    Application.ScreenUpdating = False

    With Worksheets("Data")
  
        .Visible = True

        Dim firstCell As Range
      
         Set firstCell = .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole)

        If Not firstCell Is Nothing Then
            Debug.Print .Range(firstCell, .Cells(.Rows.Count, "A").End(xlUp)).Address
        End If
      
      
    End With


End Sub



Sub Test_v4()
    Application.ScreenUpdating = False

    With Worksheets("Data")
  
        .Visible = True

        Dim firstCell As Range
      
        With .Range("A:A")
        Set firstCell = .Find("*", .Cells(.Cells.Count), xlValues, xlWhole)
        End With

        If Not firstCell Is Nothing Then
            Debug.Print .Range(firstCell, .Cells(.Rows.Count, "A").End(xlUp)).Address
        End If
      
      
    End With


End Sub

 

[MikeVol]

Compare the results of the 2 codes below

Yes, I know that. But the user used to copy from the 11th row using a script. And now the system dumps it to another row, like the 9th row. It was not specified that the sheet where the system dumps the data has a header in the first row. Even if there is a header in the first row and the system dumps the data starting from the 9th row, my second script still correctly copies the range we need.

Option Explicit

Sub Test_v2()
    Application.ScreenUpdating = False

    With Worksheets("Data")
        .Visible = True

        Dim firstCell As Range
        Set firstCell = .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole)

        If Not firstCell Is Nothing Then
            Application.Goto .Range(firstCell, .Cells(.Rows.Count, "A").End(xlUp))
            Debug.Print .Range(firstCell, .Cells(.Rows.Count, "A").End(xlUp)).Address
            .Range(firstCell, .Cells(.Rows.Count, "A").End(xlUp)).Copy

            With Worksheets("Setup").Range("B4")
                .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
            End With

            Application.CutCopyMode = False
        Else
            MsgBox "No data found in column A of the Data sheet.", vbExclamation
        End If

    End With

    Application.ScreenUpdating = True
End Sub

 

[MARK858]

Or use another method, Range.Find finds the first filled cell in column A and then selects the range.

I was just stating that the above comment is not correct, it finds the cell filled after the cell specified in the After criteria (or after the top left cell in the range if not specified in the After criteria), which is why in Test_v4 the After criteria is set as the last cell in range so it loops back to first cell so the full range is included in the search.


Going to the OP's post there is nothing to say the company data has headers or not when dumped or that it can't be dumped in A1.
I suspect it does have headers, which would mean there isn't any issue with Test_v2 unless there are cells with data above the company list (A11 in the question)..... which takes it back to the questions @Cubist asked in post 2.

 

[Peter_SSs]

@SeliM
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[SeliM]

Hi @SeliM.
We can allow the user to specify the starting line to select the desired range on the worksheet. Here is an option:

Option Explicit

Sub Test()
  
    ' Allow user to specify initial working string
    Dim startRow As Long
    startRow = Application.InputBox("Enter the starting row for selection in column A:", Type:=1)
    If startRow < 1 Then Exit Sub
    Application.ScreenUpdating = False

    With Worksheets("Data")
        .Visible = True
        .Range("A" & startRow, .Range("A" & startRow).End(xlDown)).Copy
    End With

    With Worksheets("Setup").Range("B4")
        .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Perhaps you're not looking for that, but this option works. Yes, I know, this will create extra work for you, but.

Or use another method, Range.Find finds the first filled cell in column A and then selects the range.

Option Explicit

Sub Test_v2()
    Application.ScreenUpdating = False

    With Worksheets("Data")
        .Visible = True

        Dim firstCell As Range
        Set firstCell = .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole)

        If Not firstCell Is Nothing Then
            .Range(firstCell, .Cells(.Rows.Count, "A").End(xlUp)).Copy

            With Worksheets("Setup").Range("B4")
                .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
            End With

            Application.CutCopyMode = False
        Else
            MsgBox "No data found in column A of the Data sheet.", vbExclamation
        End If

    End With

    Application.ScreenUpdating = True
End Sub

It's up to you. Glad to help. Good luck.

Thank you very much.
(Apologies for such a late reply and acknowledgement too).

I ended up using a macro to find the first component (header Company, as in a table structure) then used your colleagues guidance to find the last submission firm and copy. Works well so far (until an unexpected change in structure occurs!!)

Many many thanks to all who have helped out during the development of this project (which has been much more complex than anticipated).

Mel

 

[MikeVol]

Hi @SeliM.
So, which of the options offered to you solved your question? Your answer was unclear.