VBA - How to Replace Non-Zero Values with Column Header?

Question

I was just tweaking with a data in VBA and was stuck at one problem. I was trying to impute a zero value with a "" and a non zero value with a column header.

My data looks like :

Customer Pasta Sandwich Pizza Burger X 01 00 01 00 Y 01 01 00 01 Z 00 01 00 01 A 00 00 01 01 B 01 01 01 01

Kindly note the data is in TSV format and it has numerous columns. Basically the data is variable and one can expect different number of columns in different data. I want to build my Macro so flexible that it can be executed on any similar data.

As of now i was able to achieve the code as below :

Sub clearzero()

Dim rng As Range

For Each rng In Range("B2:XFD1048576") 'Also i have used XFD1048576 as the end of workbook ( which i know is not feasible, if anyone can suggest how to select the data dynamically then i would be glad as well )

If rng.Value = "00" Then
    rng.Value = ""
If rng.Value = "01" Then
    rng.Value =??

Next End Sub

I am not able to figure out ?? in my code. My output would be like the below:

Can anyone suggest on the same?

Thanks

Try rng.Value = Cells(1, rng.column).value (assuming your header is in row 1). Unless you expect the headers to give you a positive results with your conditions, you could simply use For Each rng In Sheet.usedRange — cybernetic.nomad
– cybernetic.nomad, Commented Jul 30, 2018 at 19:36
You do not need to write "Solved" here. Once you accept an answer, it is marked as solved. — Vityata
– Vityata, Commented Jul 31, 2018 at 10:24

Vityata · Accepted Answer · 2018-07-31 10:23:32Z

2

Sub TestMe()

    Dim myCell As Range
    Dim wks As Worksheet        
    Set wks = Worksheets(1)

    Application.EnableAnimations = False
    For Each myCell In wks.UsedRange
        If myCell = "00" Then
            myCell = ""
        ElseIf myCell = "01" Then
            myCell = wks.Cells(1, myCell.Column)
        End If
    Next myCell

    Application.EnableAnimations = True

End Sub

wks.UsedRange refers to the used range of the first worksheet
myCell = wks.Cells(1, myCell.Column) refers to the cell in the frist row of the referred cell.
Application.EnableAnimations is used to speed up a bit the process.

edited Jul 31, 2018 at 10:23

answered Jul 30, 2018 at 19:44

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

desmond.carros Over a year ago

worked like butter.. :) though it is taking time since the data is large. though i can manage with that time elapse.. thanks much!

Vityata Over a year ago

@desmond.carros - You are welcome. :) To speed it up write Application.EnableAnimations = False on the second line and Application.EnableAnimations = True on the last line.

AJD · Accepted Answer · 2018-07-30 20:06:47Z

1

A non-VBA solution.

In column E, place the formula =IF(B2>0,B$1,"") (Note the $ before the 1). You can then fill this formula across and down to cover the number of columns and rows in your original or expected data.

In terms of finding the end of your range in VBA use SpecialCells(xlCellTypeLastCell) (thanks to https://www.excelcampus.com/vba/find-last-row-column-cell/):

Sub Range_SpecialCells_Method()
    MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address
End Sub

answered Jul 30, 2018 at 20:06

AJD

2,4682 gold badges15 silver badges25 bronze badges

Collectives™ on Stack Overflow

VBA - How to Replace Non-Zero Values with Column Header?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related