Max function for all columns

Hi I'm trying to find the max value for each column with this code, but it stops after a blank row. I want the code to find the max of the entire column even if there are blank rows in the data. Any idea how to fix this code? Or is there a better way to write the code? Thanks!

'Outputting max value for each column

Sub Max_Columns()

    With Range("A2").CurrentRegion
        Range("A1").Resize(, .Columns.Count).FormulaR1C1 = "=MAX(R[2]C:R[" & .Rows.Count & "]C)"
    End With
    
    'Paste values
    Rows(1).EntireRow.Copy
    Rows(1).PasteSpecial xlPasteValues

End Sub

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

If you are trying to replace the values in each cell of row one with the max of column then try:

Option Explicit

Sub ColMaxes()
    Dim Headers As Range, _
        TestCol As Range, _
        LastRow As Long

    Set Headers = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For Each TestCol In Headers
        TestCol = WorksheetFunction.Max(TestCol.Resize(rowsize:=LastRow))
    Next TestCol
    
End Sub

EDIT: Does each column have the same number of rows?

Your original code puts a formula in row 1 then copies and paste special back to row 1? (tested). So where should the max be written?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.