Select the last cell with a value and edit macro

  • Hi,

    I need to edit these macros, to select the last row and column with a value on the ‘Data’ sheet instead of the specified O4068, as I will be adding new data into the sheet regularly and need the macro to pick up all the data automatically instead of me specifying the range every time.

    Also I need to paste the results as a value and matching the source formatting.
    I need these to apply for all 3 macros given below.

  • Re: Select the last cell with a value and edit macro

    I hope this helps. Without the actual excel sheet it is a little more difficult but the following should help you along
    To dynamically find a range you could use code such as this

    Dim lastRow As Integer
    Dim lastCol As Integer
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim xRange As Range
    Set xRange = Range("A1", Cells(lastRow, lastCol))

    This will select the range you wish to copy no matter how many rows there are or how many columns assuming the data starts in cell A1

    To past the values and their formats you would use something like this

        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False

    If this does not help upload the spreadsheet with some data and it would make things easier as I think in your code you have a lot of commands which you probably don't need. I assume this was from a macro recording?

    Hope this helps but if not as I said upload a sample sheet and I will sort it out for you

    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!