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.
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
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 :
- From your Statement above, we do know
B5
is going to be fixed, so we can use that Range Value in a WorkbookRead 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
.
- 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.
You Could provide the same DT1
variable as the Output Datatable.
- 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.