Find Last Row

[fergupa]

Hello-

I'm trying to find the last row in a worksheet. In Excel VBA the code would look something like this:

In BP, I copied the Go To Next Cell Action and tried to modify it as follows:

lastcellref = GetInstance(handle).Cells.SpecialCells(xlCellTypeLastCell).Address

However I keep getting an error message indicating xlCellTypeLastCell needs to be declared. Please advise as to what I am missing - thanks!

 

[VJR]

Hi fergupa,

In the Ms Excel VBO there is a ready made action called as 'Get Number of Rows' to do this. You can make use of it.

 

[fergupa]

VJR - thanks for your quick response and all you do on this forum. Unless I'm missing something, the 'Get Number of Rows' just finds the next empty cell. Depending on the file there could be multiple sections of empty cells before finding the last cell in the worksheet. I guess you could run a loop until next empty = A1048576 but I was hoping to shortcut this. Let me know if you have different thoughts on how to use Get Number of Rows to find the last used cell in a worksheet. Thanks!

 

[VJR]

Hi fergupa,

The 'Get Number of Rows' will show the last cell among all the columns that have data.
Apply the action on your data and post back with the results if that is not what you are looking for.

 

[fergupa]

Hi fergupa,

The 'Get Number of Rows' will show the last cell among all the columns that have data.
Apply the action on your data and post back with the results if that is not what you are looking for.

You were correct - it brought back the last row where data resides. Thanks!

 

[Bids]

How can I get the last column which has data? (i.e. "last free cell - 1" column)

 

[VJR]

How can I get the last column which has data? (i.e. "last free cell - 1" column)

Hi Bids,

Have you tried the 'Go To Next Empty Cell Right'
- Using 'Go To Cell' go to the first cell on the sheet ie; A1
- Then check what 'Go To Next Empty Cell Right' gives
- From there it will be 1 column less that has data

 

[Bids]

Hi Bids,

Have you tried the 'Go To Next Empty Cell Right'
- Using 'Go To Cell' go to the first cell on the sheet ie; A1
- Then check what 'Go To Next Empty Cell Right' gives
- From there it will be 1 column less that has data

Hi VJ, thats exactly is my question, if the next empty cell gives me H1, how do I get value G1 in my variable?

 

[VJR]

Hi VJ, thats exactly is my question, if the next empty cell gives me H1, how do I get value G1 in my variable?

Hi Bids, I will let you know on that but what is it that you want to achieve with the last column that has data?

 

[VJR]

Hi Bids, I will let you know on that but what is it that you want to achieve with the last column that has data?

So after following the above mentioned steps if you go 1 column backward you will get the last column with data (G1) in the Output parameter and take the next course of action accordingly.

View attachment 1533377252122.png

 

[fergupa]

For Last Column and Row, I used the following in the code stage:

columncount = GetWorksheet(handle,workbookname,worksheetname).Cells.SpecialCells(11).Column

rowcount = GetWorksheet(handle,workbookname,worksheetname).Cells.SpecialCells(11).Row

In VBA you can use text as the 'SpecialCells' type. For example, in VBA it would be 'Cells.SpecialCells(xlCellTypeLastCell).Column'. However I couldn't get this to work in BP and I noticed other actions used the enumeration version. I was able to find the enumeration version of xlCLTypeLastCell is equal to 11 (see here). Hope this helps - let me know if I am missing something. Thanks!

 

[fergupa]

As a continuation on this thread, I am trying to run a process that finds the first and last row/cell and selects it as a range. I then want to copy it and move it to another workbook or powerpoint. I am having difficulties selecting the range and keep getting an error stating the variable is not defined. Here is what I have in a code stage:

Dim wb, ws As Object
Dim excel, sheet, range, cells As Object
dim cellsfirstrow, cellsfirstcolumn, cellslastrow, cellslastcolumn as long

Try
wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
cellsfirstrow = firstrowcount
cellsfirstcolumn = firstcolumncount
cellslastrow= lastrowcount
cellslastcolumn=lastcolumncount
range = sheet.Range(cells(firstrowcount,firstcolumncount),cells(lastrowcount,lastcolumncount))
range.Select()
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try

The error I get is 'Object variable or With block variable not set.'. Any help/advice would be greatly appreciated. Thanks!

 

[Bids]

So after following the above mentioned steps if you go 1 column backward you will get the last column with data (G1) in the Output parameter and take the next course of action accordingly.

View attachment 1580

Thanks VJ, will try and let you know.
To answer your question, I am trying to dynamically concatenate the columns in the last column, i..e in Column H, I want to put Concat (A:G) where G & H columns are not static, but dynamic, based on the number of columns that sheet has.

 

[VJR]

I am trying to run a process that finds the first and last row/cell and selects it as a range. I then want to copy it and move it to another workbook or powerpoint.

Hi fergupa, have you tried using 'Copy Paste Worksheet as Range' which copies the entire range that is present on the sheet which can then be used to paste into another workbook?

 

[PriyaArvind]

Hi VJR, i tried using Copy Paste Worksheet as Range the only problem with it is, it copies the formula rather than actual data? is there a way we can modify to copy just the data please?

 

[VJR]

Hi PriyaArvind,

To copy only the data without formulas you need to have your stages as below-
- 'Select' action to select the source worksheet and the source range that needs to be copied
- Then the 'Copy' action to copy the selected cells onto the clipboard
- Then the 'Activate Worksheet' for the destination sheet if you want to paste this data into another sheet (or else use Activate Workbook)
- 'Go to cell' for setting the location where you want to paste this data starting from
- 'Paste' action with 'Values Only' flag as True.

If you want to avoid the hassles of all the above steps then simply go to the code stage of 'Copy and Paste Worksheet Range' by making a copy of it and making a small code change as below .

View attachment 1534996561129.png

 

[PriyaArvind]

Thanks VJ i treid Creating the new code stage for values and i get the below error

: Failed to copy worksheet: Exception from HRESULT: 0x800A03EC

 

[PriyaArvind]

View attachment 1535009786697.png

 

[VJR]

Thanks VJ i treid Creating the new code stage for values and i get the below error

: Failed to copy worksheet: Exception from HRESULT: 0x800A03EC

Hi PriyaArvind,

My fault. Paste is a method of the Worksheet object but PasteSpecial is not.
Use the below and you would see the results as expected.

View attachment 1535007514209.png

 

[PriyaArvind]

Thanks VJ for your response. Its failing again with the same reason.

: Failed to copy worksheet: Exception from HRESULT: 0x800A03EC

(I have found an alternative way of doing it by loading into collection and writing back from collection, but if this code works, it looks neat and it will be useful for others hence trying; much appreciate your time)