Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2018, 04:21 AM
rooly rooly is offline Deleting the number 0 from entire sheet Windows 10 Deleting the number 0 from entire sheet Office 2016
Novice
Deleting the number 0 from entire sheet
 
Join Date: Mar 2018
Posts: 15
rooly is on a distinguished road
Default Deleting the number 0 from entire sheet


i would be happy if there is a solution for removing the number 0 before the double digits.. throughout the Excel spreadsheet, starting from the no. 10 and so on..
Attached Images
File Type: jpg warehouse.JPG (185.8 KB, 20 views)
Attached Files
File Type: xlsx warehouse.xlsx (195.6 KB, 10 views)
Reply With Quote
  #2  
Old 05-21-2018, 05:11 AM
macropod's Avatar
macropod macropod is offline Deleting the number 0 from entire sheet Windows 7 64bit Deleting the number 0 from entire sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,225
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Try the following macro:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim i As Long
With ActiveSheet.Cells
  For i = 1 To 99
    .Replace What:="0" & Format(i, "00"), Replacement:=Format(i, "00"), LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next
End With
Application.ScreenUpdating = True
End Sub
Note: A significant drawback of what you're trying to do is that Excel will turn many of your strings into dates. If all you're concerned about is their appearance, that can be controlled by applying a suitable date format to the used range. Alternatively, you'll have to turn them all into text strings, a much slower process (so slow a progress report on the status bar is needed), using code like:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim lRow As Long, lCol As Long
Dim r As Long, c As Long, i As Long
With ActiveSheet
  With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
    lRow = .Row
    lCol = .Column
  End With
  For r = 1 To lRow
    Application.StatusBar = "Processing Row " & r
    For c = 1 To lCol
      If Left(.Cells(r, c).Text, 1) Like "[0-9]" Then .Cells(r, c).Value = "'" & .Cells(r, c).Text
    Next
    DoEvents
  Next
  For i = 1 To 99
    .Cells.Replace What:="0" & Format(i, "00"), Replacement:=Format(i, "00"), LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-24-2018, 05:57 AM
Roger Govier Roger Govier is offline Deleting the number 0 from entire sheet Windows 10 Deleting the number 0 from entire sheet Office 2016
Novice
 
Join Date: Oct 2017
Location: Abergavenny, Wales, UK
Posts: 13
Roger Govier is on a distinguished road
Default

Hi

You can do as Macropod suggests and convert all to text, but it need not be a slow process.

Writing to individual cells on the sheet in an iterative process is exceptionally slow as you have the overhead of crossing between VBA and Excel thousands of times and Reading and Writing thousands of times..
If you read all the data into an array with a single Read, process the array and then write that back as a single Write, it is almost instantaneous.

Code:
Sub RemoveZero()
    Application.ScreenUpdating = False

    Dim r As Long, c As Long
    Dim impary
    With ActiveSheet
        impary = ActiveSheet.UsedRange
 
        For r = 2 To UBound(impary, 1)
            For c = 1 To UBound(impary, 2)
                  If Len(impary(r, c)) = 8 Then
                    impary(r, c) = "'" & impary(r, c)
                Else
                    impary(r, c) = "'" & Left(impary(r, c), 3) & Mid(impary(r, c), 5, 5)
                End If
            Next
         Next
    
    ActiveSheet.Range("C4").Resize(r - 1, c - 1) = impary
    End With
    Application.ScreenUpdating = True
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting the number 0 from entire sheet How to map number of sheets one line per sheet into one sheet abbani Excel 3 12-12-2016 04:10 AM
Reuse slides deleting slide number and footer PSSMargaret PowerPoint 0 05-12-2016 05:28 AM
save sum of 2 number after deleting them tesoke Excel 3 12-17-2015 02:14 PM
Deleting the number 0 from entire sheet Excel sheet Sequence Number belloffice Excel 1 02-25-2010 02:41 PM
Deleting the number 0 from entire sheet Enter Number on any sheet one time only. paulrm906 Excel 1 04-28-2006 07:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:12 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft