#1
|
|||
|
|||
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.. |
#2
|
||||
|
||||
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 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] |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Excel sheet Sequence Number | belloffice | Excel | 1 | 02-25-2010 02:41 PM |
Enter Number on any sheet one time only. | paulrm906 | Excel | 1 | 04-28-2006 07:35 AM |