How to delete last row

How to set all non zero column cells to column header

Requirement:

The user has excel data in about 1000 columns, by anything between 5 rows deep and 1500 rows, it varies in each column, so the snippet below represents the data.

The user wants to do a Sub to replace all values in the each column with the value in each header (a1:c1 in the below) and keep going till there are no more header values, or the user can actually specify the range, but not the depth.

Sub mprnColumn()
Set MR = Range("A1:C1")

Is as far as the user gets.

Solution:

Code:

Sub ReplaceData()
    Dim x, i As Long, ii As Long, lCols As Long
    
    With ActiveSheet.Cells(1).CurrentRegion
        lCols = InputBox("There are " & .Columns.Count & " Columns of data." & vbLf & _
                        "How many columns need data changing?", "Number of Columns")
        x = .Value2
        For i = 2 To UBound(x, 1)
            For ii = 1 To lCols
                If x(i, ii) <> "" Then x(i, ii) = x(1, ii)
            Next
        Next
        .Value2 = x
    End With
        
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.