Can someone provide a simple VBA script for replacing h with space after manually selecting certain column/rows of cells

Question

Hello,

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

Answer 1

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.

Answer 2

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.