Remove zeroes from column and replace by blank

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello guys
After I run the code a sheet Combined data is created. I need your help to edit and place the code to select the columns G H and I in the combined data sheet, want to find 0 (zero), Match entire cell contents and replace them with blank.
Remove Zeroes.xlsm
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Try putting in this:
PS: Not sure why you don't just format it not to show zeroes though.

VBA Code: 
        .Columns("G:I").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula

Here:
1658105791308.png
 
Upvote 0
Solution
VBA Code: 
Sub HideZeros()
'
    Dim StartRow    As Long
    Dim LastRow     As Long
'
    StartRow = 2                                                            ' <--- Set this to the StartRow
    LastRow = Range("G" & Rows.Count).End(xlUp).Row                         ' Get last used row
'
    Range("G" & StartRow & ":I" & LastRow).NumberFormat = "0;-0;;@"         ' Hide zeros
End Sub
 
Upvote 0
Alex Blakenburg said:
Try putting in this:
PS: Not sure why you don't just format it not to show zeroes though.

VBA Code: 
        .Columns("G:I").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula

Here:
View attachment 69498
Click to expand...
It worked. Thanks Alex Blakenburg. 🙂
 
Upvote 0
Thanks for letting us know. I noticed that the Row highlighting was a bit inconsistent when some of the data was combined on the SubTotal sheet.
It would be good the get @johnnyL's buy in since he is more familiar with the code but the way it looks to me, the RGB Interior color is set in 3 places and if you make the changes below they will finish at the same column.

Rich (BB code): 
     With wsDestination
'
        .Range("A2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray           ' Display results to DestinationSheet
        DestinationLastRow = .Range("A" & .Rows.Count).End(xlUp).Row                                ' Get last row used in column A of the destination sheeet
'
'       .Range("B2:M" & DestinationLastRow).Interior.Color = RGB(146, 208, 80)                      ' Highlight the range green
'        .Range("B2:M" & DestinationLastRow).Font.Bold = True                                        ' Make the range Bold
        .Range("B2").Resize(DestinationLastRow, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)                     ' Highlight the range green
        .Range("B2").Resize(DestinationLastRow, UBound(HeaderTitlesToPaste)).Font.Bold = True                                        ' Make the range Bold

' --------------------- Lots of code between the above and the 2 sections below ---------------------------

'
    With wsMatched
        .Range("A2").Resize(UBound(MatchedArray, 1), UBound(MatchedArray, 2)) = MatchedArray        '   Display results to Matched sheet
'
        For Each cel In .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)                     '   Loop through all cells in column B on the Matched sheet
            If cel.Value = "2A" Then                                                            '       If Cell value is 'PORTAL' then ...
'                cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                    '           Color the row
'                cel.EntireRow.Font.Bold = True                                                      '           Bold the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)           '           Color the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Font.Bold = True                                                      '           Bold the row
            End If
        Next                                                                                        '   Loop back
    End With
'
    With wsMismatches
        .Range("A2").Resize(UBound(MismatchesArray, 1), UBound(MismatchesArray, 2)) = MismatchesArray   '   Display results to Mismatches sheet
'
        For Each cel In .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)                     '   Loop through all cells in column B on the Mismatches sheet
            If cel.Value = "2A" Then                                                            '       If Cell value is 'PORTAL' then ...
'                cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                    '           Color the row
'                cel.EntireRow.Font.Bold = True                                                      '           Bold the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)           '           Color the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Font.Bold = True                                                      '           Bold the row
            End If
        Next                                                                                        '   Loop back
    End With
 
Upvote 0
Alex Blakenburg said:
Thanks for letting us know. I noticed that the Row highlighting was a bit inconsistent when some of the data was combined on the SubTotal sheet.
It would be good the get @johnnyL's buy in since he is more familiar with the code but the way it looks to me, the RGB Interior color is set in 3 places and if you make the changes below they will finish at the same column.

Rich (BB code): 
     With wsDestination
'
        .Range("A2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray           ' Display results to DestinationSheet
        DestinationLastRow = .Range("A" & .Rows.Count).End(xlUp).Row                                ' Get last row used in column A of the destination sheeet
'
'       .Range("B2:M" & DestinationLastRow).Interior.Color = RGB(146, 208, 80)                      ' Highlight the range green
'        .Range("B2:M" & DestinationLastRow).Font.Bold = True                                        ' Make the range Bold
        .Range("B2").Resize(DestinationLastRow, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)                     ' Highlight the range green
        .Range("B2").Resize(DestinationLastRow, UBound(HeaderTitlesToPaste)).Font.Bold = True                                        ' Make the range Bold

' --------------------- Lots of code between the above and the 2 sections below ---------------------------

'
    With wsMatched
        .Range("A2").Resize(UBound(MatchedArray, 1), UBound(MatchedArray, 2)) = MatchedArray        '   Display results to Matched sheet
'
        For Each cel In .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)                     '   Loop through all cells in column B on the Matched sheet
            If cel.Value = "2A" Then                                                            '       If Cell value is 'PORTAL' then ...
'                cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                    '           Color the row
'                cel.EntireRow.Font.Bold = True                                                      '           Bold the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)           '           Color the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Font.Bold = True                                                      '           Bold the row
            End If
        Next                                                                                        '   Loop back
    End With
'
    With wsMismatches
        .Range("A2").Resize(UBound(MismatchesArray, 1), UBound(MismatchesArray, 2)) = MismatchesArray   '   Display results to Mismatches sheet
'
        For Each cel In .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)                     '   Loop through all cells in column B on the Mismatches sheet
            If cel.Value = "2A" Then                                                            '       If Cell value is 'PORTAL' then ...
'                cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                    '           Color the row
'                cel.EntireRow.Font.Bold = True                                                      '           Bold the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)           '           Color the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Font.Bold = True                                                      '           Bold the row
            End If
        Next                                                                                        '   Loop back
    End With
Click to expand...
It worked perfectly well with your post #2, but I actually got lost when I had to insert this line .UsedRange.EntireColumn.AutoFit. in 3 different sheets. But after trying at different places that too has been solved. Sorry, this was in the other post which I had posted.
 
Upvote 0
Sorry yes if you wanted to replace the 0s in all 3 sheets then putting it after all 3 occurences of Autofit should work.
The highlighting code (RGB) change was in case you wanted to fix the inconsistent highlighting with some sheets using EntireRow and some using B:M.
(I have used the no of columns in the heading which effectively results in B:N being highlighted)

Not sure what other post you are referring to though.
 
Upvote 0
Alex Blakenburg said:
Sorry yes if you wanted to replace the 0s in all 3 sheets then putting it after all 3 occurences of Autofit should work.
The highlighting code (RGB) change was in case you wanted to fix the inconsistent highlighting with some sheets using EntireRow and some using B:M.
(I have used the no of columns in the heading which effectively results in B:N being highlighted)

Not sure what other post you are referring to though.
Click to expand...
I understand what you said. Instead of highlighting the whole row till the end, your code will highlight the rows from A: N which is very nice. But I have a lot of other queries to solve. In the end, I will change the highlighting code tonight. Thanks man.
 
Upvote 0
Alex Blakenburg said:
Thanks for letting us know. I noticed that the Row highlighting was a bit inconsistent when some of the data was combined on the SubTotal sheet.
It would be good the get @johnnyL's buy in since he is more familiar with the code but the way it looks to me, the RGB Interior color is set in 3 places and if you make the changes below they will finish at the same column.

Rich (BB code): 
     With wsDestination
'
        .Range("A2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray           ' Display results to DestinationSheet
        DestinationLastRow = .Range("A" & .Rows.Count).End(xlUp).Row                                ' Get last row used in column A of the destination sheeet
'
'       .Range("B2:M" & DestinationLastRow).Interior.Color = RGB(146, 208, 80)                      ' Highlight the range green
'        .Range("B2:M" & DestinationLastRow).Font.Bold = True                                        ' Make the range Bold
        .Range("B2").Resize(DestinationLastRow, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)                     ' Highlight the range green
        .Range("B2").Resize(DestinationLastRow, UBound(HeaderTitlesToPaste)).Font.Bold = True                                        ' Make the range Bold

' --------------------- Lots of code between the above and the 2 sections below ---------------------------

'
    With wsMatched
        .Range("A2").Resize(UBound(MatchedArray, 1), UBound(MatchedArray, 2)) = MatchedArray        '   Display results to Matched sheet
'
        For Each cel In .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)                     '   Loop through all cells in column B on the Matched sheet
            If cel.Value = "2A" Then                                                            '       If Cell value is 'PORTAL' then ...
'                cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                    '           Color the row
'                cel.EntireRow.Font.Bold = True                                                      '           Bold the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)           '           Color the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Font.Bold = True                                                      '           Bold the row
            End If
        Next                                                                                        '   Loop back
    End With
'
    With wsMismatches
        .Range("A2").Resize(UBound(MismatchesArray, 1), UBound(MismatchesArray, 2)) = MismatchesArray   '   Display results to Mismatches sheet
'
        For Each cel In .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)                     '   Loop through all cells in column B on the Mismatches sheet
            If cel.Value = "2A" Then                                                            '       If Cell value is 'PORTAL' then ...
'                cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                    '           Color the row
'                cel.EntireRow.Font.Bold = True                                                      '           Bold the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Interior.Color = RGB(146, 208, 80)           '           Color the row
                cel.Resize(1, UBound(HeaderTitlesToPaste)).Font.Bold = True                                                      '           Bold the row
            End If
        Next                                                                                        '   Loop back
    End With
Click to expand...
Done. Edited and replaced at all the 3 places. It looks presentable now. Thanks Alex
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
How do I preserve the trailing zeros after the decimal point?
Replies
3
Views
456
JoeMo
J
A
  • Question
Solver Optimization Tool
Replies
1
Views
64
Eric W
Eric W
R
  • Question
Replace links after moving worksheets from one workbook to another.
Replies
2
Views
117
RockandGrohl
R
A
  • Question
VBA to replace cell value to zero for columns having Header Date
Replies
5
Views
291
HighAndWilder
H
A
  • Question
Removing blank spaces with formatting
Replies
3
Views
317
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,824
Messages
6,121,783
Members
449,049
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top