How to set all non zero column cells to column header
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to count cells in a dynamic range matching two criteria given in table headers |
| How to check if column header exists and if not then add column with that header |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.