Select Column To Last Row Of Data

[honkin]

hi

I am looking for a way to select a column to the last row of data. The problem is that the column I want to select has data only in row 1, but the sheet has data all the way down to row 633. I am using a Mac and have tried using all the standard keyboard shortcuts and also using the function key, but it simply does not do the task.

I read it is possible using VBA, but am not overly familiar with it, so just looking for another way to do it.

Basically, what I am doing is putting a formula in the first cell of the column and want to copy it down to the last cell of the column, in this case the cell is in row 633, but each file will be different, so just looking for a way it will select to the last row in the sheet which has data.

Thanks in advance

 

[Rick Rothstein]

Select the cell with the formula you want to copy down and then run this macro...

Sub CopyFormulaDownToLastRowOfData()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Range(ActiveCell, Cells(LastRow, ActiveCell.Column)).FillDown
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CopyFormulaDownToLastRowOfData) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[honkin]

Select the cell with the formula you want to copy down and then run this macro...

Sub CopyFormulaDownToLastRowOfData()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Range(ActiveCell, Cells(LastRow, ActiveCell.Column)).FillDown
End Sub

Cheers Rick, that's absolutely brilliant. One I thing I forgot to mention was I was hoping to paste the values only all the way down, but I think this still leave each cell with the formula, rather than just the value.

Is there a way for it to paste special with just the values copying down to the last data row?

The cell the formula is in will always be Y2 and from there down to the last data row, I want the values this formula generates.

I am really not sure, but spotted this code online. It is to do with paste special. Possibly this somehow in combination with what you supplied, though I've not written much VBA code before

Sub Paste_Values ()

Range (“Y2") .Copy
Range (??????) . PasteSpecial xlPasteValue


End Sub

Does that make sense at all, Rick?

cheers

 

[Rick Rothstein]

Try it like this then...

Sub CopyFormulaDownToLastRowOfData()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  With Range(ActiveCell, Cells(LastRow, ActiveCell.Column))
    .FillDown
    .Value = .Value
  End With
End Sub

 

[mohadin]

Hi you might modify the given code to this

 Sub CopyFormulaDownToLastRowOfData()    Dim LastRow As Long
    LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    With Range(Range("Y2"), Cells(LastRow, Range("Y2").Column))
        .FillDown
        .Value = .Value
    End With
End Sub

 

[mohadin]

OPPs
Sorry Rick

 

[honkin]

Try it like this then...

Sub CopyFormulaDownToLastRowOfData()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  With Range(ActiveCell, Cells(LastRow, ActiveCell.Column))
    .FillDown
    .Value = .Value
  End With
End Sub

Nailed it Rick. The beauty of not having the Y2 range in it is if I need to use it on another cell, it will work fine, so thanks very much

Take care

 

[honkin]

Hi you might modify the given code to this

 Sub CopyFormulaDownToLastRowOfData()    Dim LastRow As Long
    LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    With Range(Range("Y2"), Cells(LastRow, Range("Y2").Column))
        .FillDown
        .Value = .Value
    End With
End Sub

cheers Mohadin

That works a treat as well. The lack of range in Rick's one means I can use it anywhere. I only realised that once I had give the Y2 reference

Thanks again