With values such as those you mention in all cells in the range A1:FD11000, using the Find and Replace took 14 minutes 17 seconds.
Using the following code
Dim start, Finish start = Now() Dim i As Long, j As Long With Sheets(3).Range("A1") For i = 0 To .CurrentRegion.Rows.Count - 1 For j = 0 To .CurrentRegion.Columns.Count - 1 .Offset(i, j).Value = Replace(.Offset(i, j).Value, "h", "") Next j Next i End With Finish = Now() MsgBox start & " - " & Finish
displayed a message box with the times indicating and elapsed time of 7 minutes 18 seconds. That is, about half the time.
1 additional answer
Sort by: Most helpful
My apologies for being so late on this. Until recently I was not able to see that someone actually responded to the questions I sent. Not sure what the issue was.
Can someone provide a simple VBA script for replacing h with space after manually selecting certain column/rows of cells
Currently, we copy time phased values from MS Project that has numbers with an appended h such as; 20h, 240h, 0h, and blanks in cells, etc..
Currently, we manually highlight the cells (Columns and rows), click on ctrl H, click on replace h and then leave the with area blank so that we are left with just the number itself such as; 20, 240, 0, or blank, etc.
The issue is that it takes 20+ minutes to update the highlighted manually selected 160 columns and 11,000 rows making 44,000+ replacements. This file keeps growing slightly each week.
Can someone provide a simple VBA script for replacing h with space after manually selecting certain column/rows of cells? ...or perhaps there is some other method other than what we are using to reduce the time it takes to do the replacements
Can you show an example of the data you want to work on?
Sign in to comment