help with a dynamic range that I can use when creating a defined name

[Oberon70]

I have different statements that are different lengths and I want to be able to obtain the range based on what is in the DATA file of the statement.

I am still trying to wrap my head around RC1, but I was using the below code, which isn't working.

Sub custom_sort()

'

' custom_sort Macro

Dim No_of_Columns As Integer

Dim ColRng As Range



Set ColRng = Range("A:A")

'Set RowRng = Range("A1:AA1")



No_Of_Rows = WorksheetFunction.CountA(RowRng) - 2

No_of_Columns = WorksheetFunction.CountA(ColRng)





Range(Cell1, Cell & No_of_Columns).Select

ActiveWorkbook.Names.Add Name:="DD", RefersToR1C1:= _

"=data!R1C7:R1141C" & No_Of_Rows

ActiveWorkbook.Names("DD").Comment = ""

Any advice would be great as I have only started to learn VBA.

 

[kevin9999]

I'm struggling to understand exactly what you're trying to achieve. If you want to know how to set a range (and name it) from A1 to the last used cell in column A, then it could be done like this:

Dim rng As Range
Set rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
rng.Name = "XXXX"  '<~~ change it to something more descriptive/intuitive

Couple of quick tips, 1) don't use Integer when setting variables, use Long instead; 2) Don't use things like "DD" as range names - there's already a column DD and your code could get very muddled if you use a column name for a range name.

 

[Oberon70]

I'm struggling to understand exactly what you're trying to achieve. If you want to know how to set a range (and name it) from A1 to the last used cell in column A, then it could be done like this:

Dim rng As Range
Set rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
rng.Name = "XXXX"  '<~~ change it to something more descriptive/intuitive

Couple of quick tips, 1) don't use Integer when setting variables, use Long instead; 2) Don't use things like "DD" as range names - there's already a column DD and your code could get very muddled if you use a column name for a range name.

Thank you, for the advice. I am trying to create 2D dynamic range. The files I will be loading can have anywhere five rows with data up to 2,500. The number of columns will be the same from each report. The report I am accessing now has a range of A2:T1167.

I hope I was able to explain what I am trying to accomplish.

 

[kevin9999]

When you say the files I will be loading - how exactly are you loading the files, where are you placing the data (sheet name etc.) and what are you going to do with the data once you've loaded it. Also, your description of the current report range A2:T1167 - when you said the number of columns will always be the same from each report - does that mean every reports' range will be the columns A:T? And will there always be something in the last cell of the range (in this case in cell T1167)?

 

[Oberon70]

Okay, I receive reports from another team. There will always be data in the last cell of the range. What happens so far is the following.

1. A window opens up and allow me to select the file I want to transfer information from.
2. The sheet is always named "Data" and I copy it on a new sheet in the currently active workbook. The new sheet is named "Import Data".

What I want to do is create a Defined Name for this Data I have imported. This data is payments we have received on files, the total amount paid to date etc, the last diary note and status.

I will create two new sheets with the information.

1. Receipt Form

File Reference	Amount Paid	Net commission	GST	Net	Batch	Date Receipted	Receipt Number

The completed sheet would be accessed by co-workers to manually receipt the funds onto each file. I would transfer the data via Vlookup.

The next sheet will be a batch report. This report would create a diary note that would be loaded by our system onto the files.

Example of the note, which I created in the past via formula when I have manually completed the above.

The instalments are up-to-date. The last payment was $60.00 and was paid on 21/01/2021. The balance outstanding is $500.00. The funds were received on statement 2985.

When this is done, it will free me up to complete other tasks.

 

[kevin9999]

OK, you didn't answer my question does that mean every reports' range will be the columns A:T? so I'll assume that it does. Also assuming that you're going to paste the new data to cell A2 in the Import Data sheet. Try the code below - but first change the desired range name to whatever you want it to be.

Option Explicit
Sub SetRangeAndNameIt()
    Dim ws As Worksheet: Set ws = Sheets("Import Data")
    Dim rng As Range
    Set rng = ws.Range("A2", ws.Cells(Rows.Count, "T").End(xlUp))
    rng.Name = "PutYourNameHere"        '***** Change to your desired range name *****
End Sub

 

[Oberon70]

OK, you didn't answer my question does that mean every reports' range will be the columns A:T? so I'll assume that it does. Also assuming that you're going to paste the new data to cell A2 in the Import Data sheet. Try the code below - but first change the desired range name to whatever you want it to be.

Option Explicit
Sub SetRangeAndNameIt()
    Dim ws As Worksheet: Set ws = Sheets("Import Data")
    Dim rng As Range
    Set rng = ws.Range("A2", ws.Cells(Rows.Count, "T").End(xlUp))
    rng.Name = "PutYourNameHere"        '***** Change to your desired range name *****
End Sub

Sorry, yes the range will always be A:T. I will log onto my work and let you know how it goes. Thank you for your help.

 

[Oberon70]

I wanted to say thank you, the code you provided help me get the results I was looking for.

 

[kevin9999]

I wanted to say thank you, the code you provided help me get the results I was looking for.

That's good to hear, and thank you for the feedback. ?