VBA macro to copy last non blank cell on a certain range that has formula

[danwoltrs]

Hey all,

I have asked for help on a certain task I have been trying to finish, but no luck so far, so I decided to change my logic a bit:

I have a sheet where I fill in sales from rows 18 to 32, along with a few other cells that are always the same, and I want to copy them to a sheet called db, which is my database.

I want to copy:
B18 & last cell with value from B18:B32
I18 & last cell with value from I18:I32, it has a formula that if column B is blank, they will be blank, if not $L$5+1 which is my contract number.

Is there a code to get the last value from a range that has something written instead of a formula?

[joe31623]

This should do it -- just change the Column and StartRow and MyShtName name to appropriate values for your workbook.

Please Login or Register  to view this content.

To clarify, what that does is puts the cells you'll want to copy into a range variable. If you want the last row b/f you get to a formula, you can execute: rngToCopy.Cells.SpecialCells(xlCellTypeLastCell).Row, but I thought you'd want it in a range variable so you can easily execute: rngToCopy.Copy.

Also note that if you have in column 1 (with the code above, in sheet name specified in the above code):
row 1: 3
row 2: "hello world"
row 3: 4
row 4: 42
row 5: =A4-A3
row 6: 7
row 7: =A6-A3
.
.
.
...the rngToCopy will contain rows 2 thru 4 in column 1 (because the StartRow = 2 and the range contains all cells between StartRow and the end of the CONSECUTIVE (if there is a space, the row b/f the space will be the last cell in the range) rows that contain NO FORMULA.

I can help with any modifications.

[danwoltrs]

Hey Joe, thanks for helping - would you mind checking my workbook, I have not been able to get it to work with your macro above.

Also, I would need to copy two columns, not one. If you could kindly check my workbook and see where I have failed? :D