How to copy and paste specific dynamic range- only 1 column in Excel?

Hello, I would like to copy a specific column for example (B5:B1030) from a pivot table which reads values from Excel Power Query and paste this column into another sheet in the same Excel file. However this column is dynamic as row numbers can either increase or decrease… Hence, I cannot take this (B5:B1030) range as fixed value- the starting cell which is B5 for example will be fixed- the problem is taking the range until last non-empty cell in that column. I would be very appreciated if someone could help me about this issue.

@zeynepnaz.yardim

You can use Read Range activity and declare a Datatable variable

Now write as DatatableVaraible.Rows.Count which will return the number or the rows it contains

So that you can use in the Range like as below

“B5:B”+DatatableVariable.Rows.Count.ToString

Hope this may help you

Thanks

Many thanks for your rapid reply. Where shall I write DatatableVaraible.Rows.Count? By using assign activity or?

@zeynepnaz.yardim You can use Get Table Range Activity to get the range from a specific PIVOT table in an excel

Capture1

This activity returns the PIVOT table range, Ex: A5 - B11. Now, you can apply sub string to get the ending row value which is 11 The exp should like below

Output = Output.Substring(4,2)

The above one will give the value as 11. This ending row value can be used to pass dynamically to achieve your scenario. The exp should like below

"B5-B" + Output.ToString

The Output is the variable that contains ending row value. Now the bot reads the above exp as B5- B11

Use below doc for more info

Please find attached workflow below

Example.zip (75.0 KB)

Hello, thank you so much for detailed explanation! But how did you write the paranthesis of substring? What does 4 and 2 refer to? In my case my pivot table is in range (A3:H1030) including headings for now.

@zeynepnaz.yardim My bad ! You mentioned that ending row range might vary. In this case you can’t use sub string . Using regex we can get the ending row value (1030). Use the below expression instead of substring

Output = System.Text.RegularExpressions.Regex.Match(Output, "(?<=\d+:.*)\d+").ToString

The result of this will be 1030. Even the value varies it will be able to get it. The remaining steps were same

@zeynepnaz.yardim Attaching the updated workflow

Example.zip (75.1 KB)


Thank you very much! However I’m getting these errors as ‘unrecognized escape sequence’ for assign activity of output and expected string doesnt contain a definition for tostring… for copy/paste range activity

Also i’m using C# language in this process

Hi @zeynepnaz.yardim ,

Check the Below Steps if you still couldn’t find a Solution :

  1. From your Statement above, we do know B5 is going to be fixed, so we can use that Range Value in a Workbook Read Range Activity and get the Total Rows Count of the Datatable from that Row. We could do so like below :

As Shown in the Screenshot, Uncheck Add Headers property and Declare/Assign a Datatable variable as Output, say DT1.

  1. Next, we could use a Filter Datatable Activity and keep only the Column Required, in our case it is the first Column indicated by Index 0.
    image

You Could provide the same DT1 variable as the Output Datatable.

  1. Now, we have the Column Data we require and we can Add it to the Column of the Other Sheet like below. If we know the Column Range from where we would need to Paste/Add the Value we can Specify in the Range Section like "A1" or "B5" :

Let us know if this doesn’t work.