VBA Help with Array of text

D

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
525
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Hi, Is it possible to shorten this code as I have a hell of lot more I need to remove. My data will consist of 0.001 to 0.999, 1.01 to 9.99 and 1 to 50 with a specific word after which may or may not have a space, Any help would be greatly appreciated

Code: 
    Dim RngSelection, RngUsedRange, RngIntersect As range, x As Variant, Arr() As Variant
    Set RngSelection = Selection
    Set RngUsedRange = ActiveSheet.UsedRange
    Set RngIntersect = Intersect(RngSelection, RngUsedRange)
    If RngIntersect Is Nothing Then Set RngIntersect = RngUsedRange

        Arr = Array("0.001 WATTS ", "0.001WATTS ", "0.002 WATTS ", "0.002WATTS ", "0.003 WATTS ", "0.003WATTS ", "0.004 WATTS ", "0.004WATTS ", "0.005 WATTS ", "0.005WATTS ", "0.006 WATTS ", "0.006WATTS ", "0.007 WATTS ", "0.007WATTS ", "0.008 WATTS ", "0.008WATTS ", "0.009 WATTS ", "0.009WATTS ", "0.010 WATTS ", "0.010WATTS ", "0.011 WATTS ", "0.011WATTS ", "0.012 WATTS ", "0.012WATTS ", "0.013 WATTS ", "0.013WATTS ", "0.014 WATTS ", "0.014WATTS ", "0.015 WATTS ", "0.015WATTS ", "0.016 WATTS ", "0.016WATTS ", "0.017 WATTS ", "0.017WATTS ", "0.018 WATTS ", "0.018WATTS ", "0.019 WATTS ", "0.019WATTS ", "0.020 WATTS ", "0.020WATTS ", "0.021 WATTS ", "0.021WATTS ", "0.022 WATTS ", "0.022WATTS ", "0.023 WATTS ", "0.023WATTS ", "0.024 WATTS ", "0.024WATTS ", "0.025 WATTS ", "0.025WATTS ", "0.026 WATTS ", "0.026WATTS ", "0.027 WATTS ", "0.027WATTS ", "0.028 WATTS ", "0.028WATTS ", "0.029 WATTS ", "0.029WATTS ", "0.030 WATTS ", "0.030WATTS ", _
                    "0.031 WATTS ", "0.031WATTS ", "0.032 WATTS ", "0.032WATTS ", "0.033 WATTS ", "0.033WATTS ", "0.034 WATTS ", "0.034WATTS ", "0.035 WATTS ", "0.035WATTS ", "0.036 WATTS ", "0.036WATTS ", "0.037 WATTS ", "0.037WATTS ", "0.038 WATTS ", "0.038WATTS ", "0.039 WATTS ", "0.039WATTS ", "0.040 WATTS ", "0.040WATTS ", "0.041 WATTS ", "0.041WATTS ", "0.042 WATTS ", "0.042WATTS ", "0.043 WATTS ", "0.043WATTS ", "0.044 WATTS ", "0.044WATTS ", "0.045 WATTS ", "0.045WATTS ", "0.046 WATTS ", "0.046WATTS ", "0.047 WATTS ", "0.047WATTS ", "0.048 WATTS ", "0.048WATTS ", "0.049 WATTS ", "0.049WATTS ", "0.050 WATTS ", "0.050WATTS ", "0.051 WATTS ", "0.051WATTS ", "0.052 WATTS ", "0.052WATTS ", "0.053 WATTS ", "0.053WATTS ", "0.054 WATTS ", "0.054WATTS ", "0.055 WATTS ", "0.055WATTS ", "0.056 WATTS ", "0.056WATTS ", "0.057 WATTS ", "0.057WATTS ", "0.058 WATTS ", "0.058WATTS ", "0.059 WATTS ", "0.059WATTS ", "0.060 WATTS ", "0.060WATTS ", _
                    "0.061 WATTS ", "0.061WATTS ", "0.062 WATTS ", "0.062WATTS ", "0.063 WATTS ", "0.063WATTS ", "0.064 WATTS ", "0.064WATTS ", "0.065 WATTS ", "0.065WATTS ", "0.066 WATTS ", "0.066WATTS ", "0.067 WATTS ", "0.067WATTS ", "0.068 WATTS ", "0.068WATTS ", "0.069 WATTS ", "0.069WATTS ", "0.070 WATTS ", "0.070WATTS ", "0.071 WATTS ", "0.071WATTS ", "0.072 WATTS ", "0.072WATTS ", "0.073 WATTS ", "0.073WATTS ", "0.074 WATTS ", "0.074WATTS ", "0.075 WATTS ", "0.075WATTS ", "0.076 WATTS ", "0.076WATTS ", "0.077 WATTS ", "0.077WATTS ", "0.078 WATTS ", "0.078WATTS ", "0.079 WATTS ", "0.079WATTS ", "0.080 WATTS ", "0.080WATTS ", "0.081 WATTS ", "0.081WATTS ", "0.082 WATTS ", "0.082WATTS ", "0.083 WATTS ", "0.083WATTS ", "0.084 WATTS ", "0.084WATTS ", "0.085 WATTS ", "0.085WATTS ", "0.086 WATTS ", "0.086WATTS ", "0.087 WATTS ", "0.087WATTS ", "0.088 WATTS ", "0.088WATTS ", "0.089 WATTS ", "0.089WATTS ", "0.090 WATTS ", "0.090WATTS ", _
                    "0.091 WATTS ", "0.091WATTS ", "0.092 WATTS ", "0.092WATTS ", "0.093 WATTS ", "0.093WATTS ", "0.094 WATTS ", "0.094WATTS ", "0.095 WATTS ", "0.095WATTS ", "0.096 WATTS ", "0.096WATTS ", "0.097 WATTS ", "0.097WATTS ", "0.098 WATTS ", "0.098WATTS ", "0.099 WATTS ", "0.099WATTS ", "0.100 WATTS ", "0.100WATTS ", "0.101 WATTS ", "0.101WATTS ", "0.102 WATTS ", "0.102WATTS ", "0.103 WATTS ", "0.103WATTS ", "0.104 WATTS ", "0.104WATTS ", "0.105 WATTS ", "0.105WATTS ", "0.106 WATTS ", "0.106WATTS ", "0.107 WATTS ", "0.107WATTS ", "0.108 WATTS ", "0.108WATTS ", "0.109 WATTS ", "0.109WATTS ", "0.110 WATTS ", "0.110WATTS ", "0.111 WATTS ", "0.111WATTS ", "0.112 WATTS ", "0.112WATTS ", "0.113 WATTS ", "0.113WATTS ", "0.114 WATTS ", "0.114WATTS ", "0.115 WATTS ", "0.115WATTS ", "0.116 WATTS ", "0.116WATTS ", "0.117 WATTS ", "0.117WATTS ", "0.118 WATTS ", "0.118WATTS ", "0.119 WATTS ", "0.119WATTS ", "0.120 WATTS ", "0.120WATTS ", _
                    "0.121 WATTS ", "0.121WATTS ", "0.122 WATTS ", "0.122WATTS ", "0.123 WATTS ", "0.123WATTS ", "0.124 WATTS ", "0.124WATTS ", "0.125 WATTS ", "0.125WATTS ", "0.126 WATTS ", "0.126WATTS ", "0.127 WATTS ", "0.127WATTS ", "0.128 WATTS ", "0.128WATTS ", "0.129 WATTS ", "0.129WATTS ", "0.130 WATTS ", "0.130WATTS ", "0.131 WATTS ", "0.131WATTS ", "0.132 WATTS ", "0.132WATTS ", "0.133 WATTS ", "0.133WATTS ", "0.134 WATTS ", "0.134WATTS ", "0.135 WATTS ", "0.135WATTS ", "0.136 WATTS ", "0.136WATTS ", "0.137 WATTS ", "0.137WATTS ", "0.138 WATTS ", "0.138WATTS ", "0.139 WATTS ", "0.139WATTS ", "0.140 WATTS ", "0.140WATTS ", "0.141 WATTS ", "0.141WATTS ", "0.142 WATTS ", "0.142WATTS ", "0.143 WATTS ", "0.143WATTS ", "0.144 WATTS ", "0.144WATTS ", "0.145 WATTS ", "0.145WATTS ", "0.146 WATTS ", "0.146WATTS ", "0.147 WATTS ", "0.147WATTS ", "0.148 WATTS ", "0.148WATTS ", "0.149 WATTS ", "0.149WATTS ", "0.150 WATTS ", "0.150WATTS ", _
                    "0.151 WATTS ", "0.151WATTS ", "0.152 WATTS ", "0.152WATTS ", "0.153 WATTS ", "0.153WATTS ", "0.154 WATTS ", "0.154WATTS ", "0.155 WATTS ", "0.155WATTS ", "0.156 WATTS ", "0.156WATTS ", "0.157 WATTS ", "0.157WATTS ", "0.158 WATTS ", "0.158WATTS ", "0.159 WATTS ", "0.159WATTS ", "0.160 WATTS ", "0.160WATTS ", "0.161 WATTS ", "0.161WATTS ", "0.162 WATTS ", "0.162WATTS ", "0.163 WATTS ", "0.163WATTS ", "0.164 WATTS ", "0.164WATTS ", "0.165 WATTS ", "0.165WATTS ", "0.166 WATTS ", "0.166WATTS ", "0.167 WATTS ", "0.167WATTS ", "0.168 WATTS ", "0.168WATTS ", "0.169 WATTS ", "0.169WATTS ", "0.170 WATTS ", "0.170WATTS ", "0.171 WATTS ", "0.171WATTS ", "0.172 WATTS ", "0.172WATTS ", "0.173 WATTS ", "0.173WATTS ", "0.174 WATTS ", "0.174WATTS ", "0.175 WATTS ", "0.175WATTS ", "0.176 WATTS ", "0.176WATTS ", "0.177 WATTS ", "0.177WATTS ", "0.178 WATTS ", "0.178WATTS ", "0.179 WATTS ", "0.179WATTS ", "0.180 WATTS ", "0.180WATTS ", _
                    "0.181 WATTS ", "0.181WATTS ", "0.182 WATTS ", "0.182WATTS ", "0.183 WATTS ", "0.183WATTS ", "0.184 WATTS ", "0.184WATTS ", "0.185 WATTS ", "0.185WATTS ", "0.186 WATTS ", "0.186WATTS ", "0.187 WATTS ", "0.187WATTS ", "0.188 WATTS ", "0.188WATTS ", "0.189 WATTS ", "0.189WATTS ", "0.190 WATTS ", "0.190WATTS ", "0.191 WATTS ", "0.191WATTS ", "0.192 WATTS ", "0.192WATTS ", "0.193 WATTS ", "0.193WATTS ", "0.194 WATTS ", "0.194WATTS ", "0.195 WATTS ", "0.195WATTS ", "0.196 WATTS ", "0.196WATTS ", "0.197 WATTS ", "0.197WATTS ", "0.198 WATTS ", "0.198WATTS ", "0.199 WATTS ", "0.199WATTS ", "0.200 WATTS ", "0.200WATTS ", "0.201 WATTS ", "0.201WATTS ", "0.202 WATTS ", "0.202WATTS ", "0.203 WATTS ", "0.203WATTS ", "0.204 WATTS ", "0.204WATTS ", "0.205 WATTS ", "0.205WATTS ", "0.206 WATTS ", "0.206WATTS ", "0.207 WATTS ", "0.207WATTS ", "0.208 WATTS ", "0.208WATTS ", "0.209 WATTS ", "0.209WATTS ", "0.210 WATTS ", "0.210WATTS ", _
                    "0.211 WATTS ", "0.211WATTS", "0.212 WATTS ", "0.212WATTS", "0.213 WATTS ", "0.213WATTS", "0.214 WATTS ", "0.214WATTS", "0.215 WATTS ", "0.215WATTS", "0.216 WATTS ", "0.216WATTS", "0.217 WATTS ", "0.217WATTS", "0.218 WATTS ", "0.218WATTS", "0.219 WATTS ", "0.219WATTS", "0.220 WATTS ", "0.220WATTS", "0.221 WATTS ", "0.221WATTS", "0.222 WATTS ", "0.222WATTS", "0.223 WATTS ", "0.223WATTS", "0.224 WATTS ", "0.224WATTS", "0.225 WATTS ", "0.225WATTS", "0.226 WATTS ", "0.226WATTS", "0.227 WATTS ", "0.227WATTS", "0.228 WATTS ", "0.228WATTS", "0.229 WATTS ", "0.229WATTS", "0.230 WATTS ", "0.230WATTS", "0.231 WATTS ", "0.231WATTS", "0.232 WATTS ", "0.232WATTS", "0.233 WATTS ", "0.233WATTS", "0.234 WATTS ", "0.234WATTS", "0.235 WATTS ", "0.235WATTS", "0.236 WATTS ", "0.236WATTS", "0.237 WATTS ", "0.237WATTS", "0.238 WATTS ", "0.238WATTS", "0.239 WATTS ", "0.239WATTS", "0.240 WATTS ", "0.240WATTS", _
                    "0.241 WATTS ", "0.241WATTS", "0.242 WATTS ", "0.242WATTS", "0.243 WATTS ", "0.243WATTS", "0.244 WATTS ", "0.244WATTS", "0.245 WATTS ", "0.245WATTS", "0.246 WATTS ", "0.246WATTS", "0.247 WATTS ", "0.247WATTS", "0.248 WATTS ", "0.248WATTS", "0.249 WATTS ", "0.249WATTS", "0.250 WATTS ", "0.250WATTS", "0.251 WATTS ", "0.251WATTS", "0.252 WATTS ", "0.252WATTS", "0.253 WATTS ", "0.253WATTS", "0.254 WATTS ", "0.254WATTS", "0.255 WATTS ", "0.255WATTS", "0.256 WATTS ", "0.256WATTS", "0.257 WATTS ", "0.257WATTS", "0.258 WATTS ", "0.258WATTS", "0.259 WATTS ", "0.259WATTS", "0.260 WATTS ", "0.260WATTS", "0.261 WATTS ", "0.261WATTS", "0.262 WATTS ", "0.262WATTS", "0.263 WATTS ", "0.263WATTS", "0.264 WATTS ", "0.264WATTS", "0.265 WATTS ", "0.265WATTS", "0.266 WATTS ", "0.266WATTS", "0.267 WATTS ", "0.267WATTS", "0.268 WATTS ", "0.268WATTS", "0.269 WATTS ", "0.269WATTS", "0.270 WATTS ", "0.270WATTS", _
                    "0.271 WATTS ", "0.271WATTS", "0.272 WATTS ", "0.272WATTS", "0.273 WATTS ", "0.273WATTS", "0.274 WATTS ", "0.274WATTS", "0.275 WATTS ", "0.275WATTS", "0.276 WATTS ", "0.276WATTS", "0.277 WATTS ", "0.277WATTS", "0.278 WATTS ", "0.278WATTS", "0.279 WATTS ", "0.279WATTS", "0.280 WATTS ", "0.280WATTS", "0.281 WATTS ", "0.281WATTS", "0.282 WATTS ", "0.282WATTS", "0.283 WATTS ", "0.283WATTS", "0.284 WATTS ", "0.284WATTS", "0.285 WATTS ", "0.285WATTS", "0.286 WATTS ", "0.286WATTS", "0.287 WATTS ", "0.287WATTS", "0.288 WATTS ", "0.288WATTS", "0.289 WATTS ", "0.289WATTS", "0.290 WATTS ", "0.290WATTS", "0.291 WATTS ", "0.291WATTS", "0.292 WATTS ", "0.292WATTS", "0.293 WATTS ", "0.293WATTS", "0.294 WATTS ", "0.294WATTS", "0.295 WATTS ", "0.295WATTS", "0.296 WATTS ", "0.296WATTS", "0.297 WATTS ", "0.297WATTS", "0.298 WATTS ", "0.298WATTS", "0.299 WATTS ", "0.299WATTS", "0.300 WATTS ", "0.300WATTS", _
                    "0.301 WATTS ", "0.301WATTS", "0.302 WATTS ", "0.302WATTS", "0.303 WATTS ", "0.303WATTS", "0.304 WATTS ", "0.304WATTS", "0.305 WATTS ", "0.305WATTS", "0.306 WATTS ", "0.306WATTS", "0.307 WATTS ", "0.307WATTS", "0.308 WATTS ", "0.308WATTS", "0.309 WATTS ", "0.309WATTS", "0.310 WATTS ", "0.310WATTS", "0.311 WATTS ", "0.311WATTS", "0.312 WATTS ", "0.312WATTS", "0.313 WATTS ", "0.313WATTS", "0.314 WATTS ", "0.314WATTS", "0.315 WATTS ", "0.315WATTS", "0.316 WATTS ", "0.316WATTS", "0.317 WATTS ", "0.317WATTS", "0.318 WATTS ", "0.318WATTS", "0.319 WATTS ", "0.319WATTS", "0.320 WATTS ", "0.320WATTS", "0.321 WATTS ", "0.321WATTS", "0.322 WATTS ", "0.322WATTS", "0.323 WATTS ", "0.323WATTS", "0.324 WATTS ", "0.324WATTS", "0.325 WATTS ", "0.325WATTS", "0.326 WATTS ", "0.326WATTS", "0.327 WATTS ", "0.327WATTS", "0.328 WATTS ", "0.328WATTS", "0.329 WATTS ", "0.329WATTS", "0.330 WATTS ", "0.330WATTS", _
                    "0.331 WATTS ", "0.331WATTS", "0.332 WATTS ", "0.332WATTS", "0.333 WATTS ", "0.333WATTS", "0.334 WATTS ", "0.334WATTS", "0.335 WATTS ", "0.335WATTS", "0.336 WATTS ", "0.336WATTS", "0.337 WATTS ", "0.337WATTS", "0.338 WATTS ", "0.338WATTS", "0.339 WATTS ", "0.339WATTS", "0.340 WATTS ", "0.340WATTS", "0.341 WATTS ", "0.341WATTS", "0.342 WATTS ", "0.342WATTS", "0.343 WATTS ", "0.343WATTS", "0.344 WATTS ", "0.344WATTS", "0.345 WATTS ", "0.345WATTS", "0.346 WATTS ", "0.346WATTS", "0.347 WATTS ", "0.347WATTS", "0.348 WATTS ", "0.348WATTS", "0.349 WATTS ", "0.349WATTS", "0.350 WATTS ", "0.350WATTS", "0.351 WATTS ", "0.351WATTS", "0.352 WATTS ", "0.352WATTS", "0.353 WATTS ", "0.353WATTS", "0.354 WATTS ", "0.354WATTS", "0.355 WATTS ", "0.355WATTS", "0.356 WATTS ", "0.356WATTS", "0.357 WATTS ", "0.357WATTS", "0.358 WATTS ", "0.358WATTS", "0.359 WATTS ", "0.359WATTS", "0.360 WATTS ", "0.360WATTS", _
                    "0.361 WATTS ", "0.361WATTS", "0.362 WATTS ", "0.362WATTS", "0.363 WATTS ", "0.363WATTS", "0.364 WATTS ", "0.364WATTS", "0.365 WATTS ", "0.365WATTS", "0.366 WATTS ", "0.366WATTS", "0.367 WATTS ", "0.367WATTS", "0.368 WATTS ", "0.368WATTS", "0.369 WATTS ", "0.369WATTS", "0.370 WATTS ", "0.370WATTS", "0.371 WATTS ", "0.371WATTS", "0.372 WATTS ", "0.372WATTS", "0.373 WATTS ", "0.373WATTS", "0.374 WATTS ", "0.374WATTS", "0.375 WATTS ", "0.375WATTS", "0.376 WATTS ", "0.376WATTS", "0.377 WATTS ", "0.377WATTS", "0.378 WATTS ", "0.378WATTS", "0.379 WATTS ", "0.379WATTS", "0.380 WATTS ", "0.380WATTS", "0.381 WATTS ", "0.381WATTS", "0.382 WATTS ", "0.382WATTS", "0.383 WATTS ", "0.383WATTS", "0.384 WATTS ", "0.384WATTS", "0.385 WATTS ", "0.385WATTS", "0.386 WATTS ", "0.386WATTS", "0.387 WATTS ", "0.387WATTS", "0.388 WATTS ", "0.388WATTS", "0.389 WATTS ", "0.389WATTS", "0.390 WATTS ", "0.390WATTS", _
                    "0.391 WATTS ", "0.391WATTS", "0.392 WATTS ", "0.392WATTS", "0.393 WATTS ", "0.393WATTS", "0.394 WATTS ", "0.394WATTS", "0.395 WATTS ", "0.395WATTS", "0.396 WATTS ", "0.396WATTS", "0.397 WATTS ", "0.397WATTS", "0.398 WATTS ", "0.398WATTS", "0.399 WATTS ", "0.399WATTS", "0.400 WATTS ", "0.400WATTS")
    Application.ScreenUpdating = False
    For Each x In Arr
    With RngIntersect
        .Replace What:=x, Replacement:="", LookAt:=xlPart
            End With
                Next x
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Try this to begin with:
Code: 
Sub M()

    Dim r   As Range
    Dim arr()   As Variant
    Dim x       As Long
    Dim y       As Long
    Dim i       As Double
    
    Set r = Selection
    If Intersect(Selection, ActiveSheet.UsedRange) Is Nothing Then Set r = ActiveSheet.UsedRange
    
    r.Replace what:=" WATTS ", replacement:="WATTS "
    arr = r.Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            For i = 0.001 To 0.999 Step 0.001
                arr(x, y) = Replace(arr(x, y), CStr(i) & "WATTS ", "")
            Next i
        Next y
    Next x

    r.Resize(UBound(arr, 1), UBound(arr, 2)).Value = r
    
    Erase arr
    Set r = Nothing
    
End Sub
 
Last edited:
Upvote 0
Hi JackDanIce, The code errors with Run-time Error 13: Type Mismatch

Code: 
    arr = r.Value
 
Upvote 0
I do not get that error, made a couple of changes though (a loop escape if all cells tested), try:
Code: 
Sub M()

    Dim r   As Range
    
    Dim arr()   As Variant
    Dim x       As Long
    Dim y       As Long
    Dim i       As Double
    Dim e       As Long
    
    Set r = Selection
    If Intersect(Selection, ActiveSheet.UsedRange) Is Nothing Then Set r = ActiveSheet.UsedRange
    
    With r
        .Replace what:=" WATTS ", replacement:="WATTS "
        arr = .Value
    End With
                
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            For i = 0.001 To 0.999 Step 0.001
                e = e + 1
                arr(x, y) = Replace(arr(x, y), CStr(i) & String(5 - Len(CStr(i)), "0") & "WATTS ", "")
                If e > UBound(arr, 1) * UBound(arr, 2) Then Exit For
            Next i
        Next y
    Next x

    r.Resize(UBound(arr, 1), UBound(arr, 2)).Value = r
    
    Erase arr
    Set r = Nothing

End Sub
 
Last edited:
Upvote 0
I am now getting Run time error 1004: Application-defined or object-defined error on this line

Code: 
    r.Resize(UBound(arr, 1), UBound(arr, 2)).Value = r
 
Upvote 0
Typo, try:
Rich (BB code): 
    r.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
 
Upvote 0
Have now tried it and nothing happens, Perhaps it's because what I am looking for is within a string of words.
 
Upvote 0
I don't really see the need for the array at all - you can use a simply loop:

Code: 
    For x = 1 to 999
    RngIntersect.Replace What:="0." & format(x, "000") & " WATTS ", Replacement:="", LookAt:=xlPart
    RngIntersect.Replace What:="0." & format(x, "000") & "WATTS ", Replacement:="", LookAt:=xlPart
                Next x
 
Last edited:
Upvote 0
Hi RoryA, Where do I need to insert these lines of code?
 
Upvote 0
They replace this part:

Code: 
        Arr = Array("0.001 WATTS ", "0.001WATTS ", "0.002 WATTS ", "0.002WATTS ", "0.003 WATTS ", "0.003WATTS ", "0.004 WATTS ", "0.004WATTS ", "0.005 WATTS ", "0.005WATTS ", "0.006 WATTS ", "0.006WATTS ", "0.007 WATTS ", "0.007WATTS ", "0.008 WATTS ", "0.008WATTS ", "0.009 WATTS ", "0.009WATTS ", "0.010 WATTS ", "0.010WATTS ", "0.011 WATTS ", "0.011WATTS ", "0.012 WATTS ", "0.012WATTS ", "0.013 WATTS ", "0.013WATTS ", "0.014 WATTS ", "0.014WATTS ", "0.015 WATTS ", "0.015WATTS ", "0.016 WATTS ", "0.016WATTS ", "0.017 WATTS ", "0.017WATTS ", "0.018 WATTS ", "0.018WATTS ", "0.019 WATTS ", "0.019WATTS ", "0.020 WATTS ", "0.020WATTS ", "0.021 WATTS ", "0.021WATTS ", "0.022 WATTS ", "0.022WATTS ", "0.023 WATTS ", "0.023WATTS ", "0.024 WATTS ", "0.024WATTS ", "0.025 WATTS ", "0.025WATTS ", "0.026 WATTS ", "0.026WATTS ", "0.027 WATTS ", "0.027WATTS ", "0.028 WATTS ", "0.028WATTS ", "0.029 WATTS ", "0.029WATTS ", "0.030 WATTS ", "0.030WATTS ", _
                    "0.031 WATTS ", "0.031WATTS ", "0.032 WATTS ", "0.032WATTS ", "0.033 WATTS ", "0.033WATTS ", "0.034 WATTS ", "0.034WATTS ", "0.035 WATTS ", "0.035WATTS ", "0.036 WATTS ", "0.036WATTS ", "0.037 WATTS ", "0.037WATTS ", "0.038 WATTS ", "0.038WATTS ", "0.039 WATTS ", "0.039WATTS ", "0.040 WATTS ", "0.040WATTS ", "0.041 WATTS ", "0.041WATTS ", "0.042 WATTS ", "0.042WATTS ", "0.043 WATTS ", "0.043WATTS ", "0.044 WATTS ", "0.044WATTS ", "0.045 WATTS ", "0.045WATTS ", "0.046 WATTS ", "0.046WATTS ", "0.047 WATTS ", "0.047WATTS ", "0.048 WATTS ", "0.048WATTS ", "0.049 WATTS ", "0.049WATTS ", "0.050 WATTS ", "0.050WATTS ", "0.051 WATTS ", "0.051WATTS ", "0.052 WATTS ", "0.052WATTS ", "0.053 WATTS ", "0.053WATTS ", "0.054 WATTS ", "0.054WATTS ", "0.055 WATTS ", "0.055WATTS ", "0.056 WATTS ", "0.056WATTS ", "0.057 WATTS ", "0.057WATTS ", "0.058 WATTS ", "0.058WATTS ", "0.059 WATTS ", "0.059WATTS ", "0.060 WATTS ", "0.060WATTS ", _
                    "0.061 WATTS ", "0.061WATTS ", "0.062 WATTS ", "0.062WATTS ", "0.063 WATTS ", "0.063WATTS ", "0.064 WATTS ", "0.064WATTS ", "0.065 WATTS ", "0.065WATTS ", "0.066 WATTS ", "0.066WATTS ", "0.067 WATTS ", "0.067WATTS ", "0.068 WATTS ", "0.068WATTS ", "0.069 WATTS ", "0.069WATTS ", "0.070 WATTS ", "0.070WATTS ", "0.071 WATTS ", "0.071WATTS ", "0.072 WATTS ", "0.072WATTS ", "0.073 WATTS ", "0.073WATTS ", "0.074 WATTS ", "0.074WATTS ", "0.075 WATTS ", "0.075WATTS ", "0.076 WATTS ", "0.076WATTS ", "0.077 WATTS ", "0.077WATTS ", "0.078 WATTS ", "0.078WATTS ", "0.079 WATTS ", "0.079WATTS ", "0.080 WATTS ", "0.080WATTS ", "0.081 WATTS ", "0.081WATTS ", "0.082 WATTS ", "0.082WATTS ", "0.083 WATTS ", "0.083WATTS ", "0.084 WATTS ", "0.084WATTS ", "0.085 WATTS ", "0.085WATTS ", "0.086 WATTS ", "0.086WATTS ", "0.087 WATTS ", "0.087WATTS ", "0.088 WATTS ", "0.088WATTS ", "0.089 WATTS ", "0.089WATTS ", "0.090 WATTS ", "0.090WATTS ", _
                    "0.091 WATTS ", "0.091WATTS ", "0.092 WATTS ", "0.092WATTS ", "0.093 WATTS ", "0.093WATTS ", "0.094 WATTS ", "0.094WATTS ", "0.095 WATTS ", "0.095WATTS ", "0.096 WATTS ", "0.096WATTS ", "0.097 WATTS ", "0.097WATTS ", "0.098 WATTS ", "0.098WATTS ", "0.099 WATTS ", "0.099WATTS ", "0.100 WATTS ", "0.100WATTS ", "0.101 WATTS ", "0.101WATTS ", "0.102 WATTS ", "0.102WATTS ", "0.103 WATTS ", "0.103WATTS ", "0.104 WATTS ", "0.104WATTS ", "0.105 WATTS ", "0.105WATTS ", "0.106 WATTS ", "0.106WATTS ", "0.107 WATTS ", "0.107WATTS ", "0.108 WATTS ", "0.108WATTS ", "0.109 WATTS ", "0.109WATTS ", "0.110 WATTS ", "0.110WATTS ", "0.111 WATTS ", "0.111WATTS ", "0.112 WATTS ", "0.112WATTS ", "0.113 WATTS ", "0.113WATTS ", "0.114 WATTS ", "0.114WATTS ", "0.115 WATTS ", "0.115WATTS ", "0.116 WATTS ", "0.116WATTS ", "0.117 WATTS ", "0.117WATTS ", "0.118 WATTS ", "0.118WATTS ", "0.119 WATTS ", "0.119WATTS ", "0.120 WATTS ", "0.120WATTS ", _
                    "0.121 WATTS ", "0.121WATTS ", "0.122 WATTS ", "0.122WATTS ", "0.123 WATTS ", "0.123WATTS ", "0.124 WATTS ", "0.124WATTS ", "0.125 WATTS ", "0.125WATTS ", "0.126 WATTS ", "0.126WATTS ", "0.127 WATTS ", "0.127WATTS ", "0.128 WATTS ", "0.128WATTS ", "0.129 WATTS ", "0.129WATTS ", "0.130 WATTS ", "0.130WATTS ", "0.131 WATTS ", "0.131WATTS ", "0.132 WATTS ", "0.132WATTS ", "0.133 WATTS ", "0.133WATTS ", "0.134 WATTS ", "0.134WATTS ", "0.135 WATTS ", "0.135WATTS ", "0.136 WATTS ", "0.136WATTS ", "0.137 WATTS ", "0.137WATTS ", "0.138 WATTS ", "0.138WATTS ", "0.139 WATTS ", "0.139WATTS ", "0.140 WATTS ", "0.140WATTS ", "0.141 WATTS ", "0.141WATTS ", "0.142 WATTS ", "0.142WATTS ", "0.143 WATTS ", "0.143WATTS ", "0.144 WATTS ", "0.144WATTS ", "0.145 WATTS ", "0.145WATTS ", "0.146 WATTS ", "0.146WATTS ", "0.147 WATTS ", "0.147WATTS ", "0.148 WATTS ", "0.148WATTS ", "0.149 WATTS ", "0.149WATTS ", "0.150 WATTS ", "0.150WATTS ", _
                    "0.151 WATTS ", "0.151WATTS ", "0.152 WATTS ", "0.152WATTS ", "0.153 WATTS ", "0.153WATTS ", "0.154 WATTS ", "0.154WATTS ", "0.155 WATTS ", "0.155WATTS ", "0.156 WATTS ", "0.156WATTS ", "0.157 WATTS ", "0.157WATTS ", "0.158 WATTS ", "0.158WATTS ", "0.159 WATTS ", "0.159WATTS ", "0.160 WATTS ", "0.160WATTS ", "0.161 WATTS ", "0.161WATTS ", "0.162 WATTS ", "0.162WATTS ", "0.163 WATTS ", "0.163WATTS ", "0.164 WATTS ", "0.164WATTS ", "0.165 WATTS ", "0.165WATTS ", "0.166 WATTS ", "0.166WATTS ", "0.167 WATTS ", "0.167WATTS ", "0.168 WATTS ", "0.168WATTS ", "0.169 WATTS ", "0.169WATTS ", "0.170 WATTS ", "0.170WATTS ", "0.171 WATTS ", "0.171WATTS ", "0.172 WATTS ", "0.172WATTS ", "0.173 WATTS ", "0.173WATTS ", "0.174 WATTS ", "0.174WATTS ", "0.175 WATTS ", "0.175WATTS ", "0.176 WATTS ", "0.176WATTS ", "0.177 WATTS ", "0.177WATTS ", "0.178 WATTS ", "0.178WATTS ", "0.179 WATTS ", "0.179WATTS ", "0.180 WATTS ", "0.180WATTS ", _
                    "0.181 WATTS ", "0.181WATTS ", "0.182 WATTS ", "0.182WATTS ", "0.183 WATTS ", "0.183WATTS ", "0.184 WATTS ", "0.184WATTS ", "0.185 WATTS ", "0.185WATTS ", "0.186 WATTS ", "0.186WATTS ", "0.187 WATTS ", "0.187WATTS ", "0.188 WATTS ", "0.188WATTS ", "0.189 WATTS ", "0.189WATTS ", "0.190 WATTS ", "0.190WATTS ", "0.191 WATTS ", "0.191WATTS ", "0.192 WATTS ", "0.192WATTS ", "0.193 WATTS ", "0.193WATTS ", "0.194 WATTS ", "0.194WATTS ", "0.195 WATTS ", "0.195WATTS ", "0.196 WATTS ", "0.196WATTS ", "0.197 WATTS ", "0.197WATTS ", "0.198 WATTS ", "0.198WATTS ", "0.199 WATTS ", "0.199WATTS ", "0.200 WATTS ", "0.200WATTS ", "0.201 WATTS ", "0.201WATTS ", "0.202 WATTS ", "0.202WATTS ", "0.203 WATTS ", "0.203WATTS ", "0.204 WATTS ", "0.204WATTS ", "0.205 WATTS ", "0.205WATTS ", "0.206 WATTS ", "0.206WATTS ", "0.207 WATTS ", "0.207WATTS ", "0.208 WATTS ", "0.208WATTS ", "0.209 WATTS ", "0.209WATTS ", "0.210 WATTS ", "0.210WATTS ", _
                    "0.211 WATTS ", "0.211WATTS", "0.212 WATTS ", "0.212WATTS", "0.213 WATTS ", "0.213WATTS", "0.214 WATTS ", "0.214WATTS", "0.215 WATTS ", "0.215WATTS", "0.216 WATTS ", "0.216WATTS", "0.217 WATTS ", "0.217WATTS", "0.218 WATTS ", "0.218WATTS", "0.219 WATTS ", "0.219WATTS", "0.220 WATTS ", "0.220WATTS", "0.221 WATTS ", "0.221WATTS", "0.222 WATTS ", "0.222WATTS", "0.223 WATTS ", "0.223WATTS", "0.224 WATTS ", "0.224WATTS", "0.225 WATTS ", "0.225WATTS", "0.226 WATTS ", "0.226WATTS", "0.227 WATTS ", "0.227WATTS", "0.228 WATTS ", "0.228WATTS", "0.229 WATTS ", "0.229WATTS", "0.230 WATTS ", "0.230WATTS", "0.231 WATTS ", "0.231WATTS", "0.232 WATTS ", "0.232WATTS", "0.233 WATTS ", "0.233WATTS", "0.234 WATTS ", "0.234WATTS", "0.235 WATTS ", "0.235WATTS", "0.236 WATTS ", "0.236WATTS", "0.237 WATTS ", "0.237WATTS", "0.238 WATTS ", "0.238WATTS", "0.239 WATTS ", "0.239WATTS", "0.240 WATTS ", "0.240WATTS", _
                    "0.241 WATTS ", "0.241WATTS", "0.242 WATTS ", "0.242WATTS", "0.243 WATTS ", "0.243WATTS", "0.244 WATTS ", "0.244WATTS", "0.245 WATTS ", "0.245WATTS", "0.246 WATTS ", "0.246WATTS", "0.247 WATTS ", "0.247WATTS", "0.248 WATTS ", "0.248WATTS", "0.249 WATTS ", "0.249WATTS", "0.250 WATTS ", "0.250WATTS", "0.251 WATTS ", "0.251WATTS", "0.252 WATTS ", "0.252WATTS", "0.253 WATTS ", "0.253WATTS", "0.254 WATTS ", "0.254WATTS", "0.255 WATTS ", "0.255WATTS", "0.256 WATTS ", "0.256WATTS", "0.257 WATTS ", "0.257WATTS", "0.258 WATTS ", "0.258WATTS", "0.259 WATTS ", "0.259WATTS", "0.260 WATTS ", "0.260WATTS", "0.261 WATTS ", "0.261WATTS", "0.262 WATTS ", "0.262WATTS", "0.263 WATTS ", "0.263WATTS", "0.264 WATTS ", "0.264WATTS", "0.265 WATTS ", "0.265WATTS", "0.266 WATTS ", "0.266WATTS", "0.267 WATTS ", "0.267WATTS", "0.268 WATTS ", "0.268WATTS", "0.269 WATTS ", "0.269WATTS", "0.270 WATTS ", "0.270WATTS", _
                    "0.271 WATTS ", "0.271WATTS", "0.272 WATTS ", "0.272WATTS", "0.273 WATTS ", "0.273WATTS", "0.274 WATTS ", "0.274WATTS", "0.275 WATTS ", "0.275WATTS", "0.276 WATTS ", "0.276WATTS", "0.277 WATTS ", "0.277WATTS", "0.278 WATTS ", "0.278WATTS", "0.279 WATTS ", "0.279WATTS", "0.280 WATTS ", "0.280WATTS", "0.281 WATTS ", "0.281WATTS", "0.282 WATTS ", "0.282WATTS", "0.283 WATTS ", "0.283WATTS", "0.284 WATTS ", "0.284WATTS", "0.285 WATTS ", "0.285WATTS", "0.286 WATTS ", "0.286WATTS", "0.287 WATTS ", "0.287WATTS", "0.288 WATTS ", "0.288WATTS", "0.289 WATTS ", "0.289WATTS", "0.290 WATTS ", "0.290WATTS", "0.291 WATTS ", "0.291WATTS", "0.292 WATTS ", "0.292WATTS", "0.293 WATTS ", "0.293WATTS", "0.294 WATTS ", "0.294WATTS", "0.295 WATTS ", "0.295WATTS", "0.296 WATTS ", "0.296WATTS", "0.297 WATTS ", "0.297WATTS", "0.298 WATTS ", "0.298WATTS", "0.299 WATTS ", "0.299WATTS", "0.300 WATTS ", "0.300WATTS", _
                    "0.301 WATTS ", "0.301WATTS", "0.302 WATTS ", "0.302WATTS", "0.303 WATTS ", "0.303WATTS", "0.304 WATTS ", "0.304WATTS", "0.305 WATTS ", "0.305WATTS", "0.306 WATTS ", "0.306WATTS", "0.307 WATTS ", "0.307WATTS", "0.308 WATTS ", "0.308WATTS", "0.309 WATTS ", "0.309WATTS", "0.310 WATTS ", "0.310WATTS", "0.311 WATTS ", "0.311WATTS", "0.312 WATTS ", "0.312WATTS", "0.313 WATTS ", "0.313WATTS", "0.314 WATTS ", "0.314WATTS", "0.315 WATTS ", "0.315WATTS", "0.316 WATTS ", "0.316WATTS", "0.317 WATTS ", "0.317WATTS", "0.318 WATTS ", "0.318WATTS", "0.319 WATTS ", "0.319WATTS", "0.320 WATTS ", "0.320WATTS", "0.321 WATTS ", "0.321WATTS", "0.322 WATTS ", "0.322WATTS", "0.323 WATTS ", "0.323WATTS", "0.324 WATTS ", "0.324WATTS", "0.325 WATTS ", "0.325WATTS", "0.326 WATTS ", "0.326WATTS", "0.327 WATTS ", "0.327WATTS", "0.328 WATTS ", "0.328WATTS", "0.329 WATTS ", "0.329WATTS", "0.330 WATTS ", "0.330WATTS", _
                    "0.331 WATTS ", "0.331WATTS", "0.332 WATTS ", "0.332WATTS", "0.333 WATTS ", "0.333WATTS", "0.334 WATTS ", "0.334WATTS", "0.335 WATTS ", "0.335WATTS", "0.336 WATTS ", "0.336WATTS", "0.337 WATTS ", "0.337WATTS", "0.338 WATTS ", "0.338WATTS", "0.339 WATTS ", "0.339WATTS", "0.340 WATTS ", "0.340WATTS", "0.341 WATTS ", "0.341WATTS", "0.342 WATTS ", "0.342WATTS", "0.343 WATTS ", "0.343WATTS", "0.344 WATTS ", "0.344WATTS", "0.345 WATTS ", "0.345WATTS", "0.346 WATTS ", "0.346WATTS", "0.347 WATTS ", "0.347WATTS", "0.348 WATTS ", "0.348WATTS", "0.349 WATTS ", "0.349WATTS", "0.350 WATTS ", "0.350WATTS", "0.351 WATTS ", "0.351WATTS", "0.352 WATTS ", "0.352WATTS", "0.353 WATTS ", "0.353WATTS", "0.354 WATTS ", "0.354WATTS", "0.355 WATTS ", "0.355WATTS", "0.356 WATTS ", "0.356WATTS", "0.357 WATTS ", "0.357WATTS", "0.358 WATTS ", "0.358WATTS", "0.359 WATTS ", "0.359WATTS", "0.360 WATTS ", "0.360WATTS", _
                    "0.361 WATTS ", "0.361WATTS", "0.362 WATTS ", "0.362WATTS", "0.363 WATTS ", "0.363WATTS", "0.364 WATTS ", "0.364WATTS", "0.365 WATTS ", "0.365WATTS", "0.366 WATTS ", "0.366WATTS", "0.367 WATTS ", "0.367WATTS", "0.368 WATTS ", "0.368WATTS", "0.369 WATTS ", "0.369WATTS", "0.370 WATTS ", "0.370WATTS", "0.371 WATTS ", "0.371WATTS", "0.372 WATTS ", "0.372WATTS", "0.373 WATTS ", "0.373WATTS", "0.374 WATTS ", "0.374WATTS", "0.375 WATTS ", "0.375WATTS", "0.376 WATTS ", "0.376WATTS", "0.377 WATTS ", "0.377WATTS", "0.378 WATTS ", "0.378WATTS", "0.379 WATTS ", "0.379WATTS", "0.380 WATTS ", "0.380WATTS", "0.381 WATTS ", "0.381WATTS", "0.382 WATTS ", "0.382WATTS", "0.383 WATTS ", "0.383WATTS", "0.384 WATTS ", "0.384WATTS", "0.385 WATTS ", "0.385WATTS", "0.386 WATTS ", "0.386WATTS", "0.387 WATTS ", "0.387WATTS", "0.388 WATTS ", "0.388WATTS", "0.389 WATTS ", "0.389WATTS", "0.390 WATTS ", "0.390WATTS", _
                    "0.391 WATTS ", "0.391WATTS", "0.392 WATTS ", "0.392WATTS", "0.393 WATTS ", "0.393WATTS", "0.394 WATTS ", "0.394WATTS", "0.395 WATTS ", "0.395WATTS", "0.396 WATTS ", "0.396WATTS", "0.397 WATTS ", "0.397WATTS", "0.398 WATTS ", "0.398WATTS", "0.399 WATTS ", "0.399WATTS", "0.400 WATTS ", "0.400WATTS")
    Application.ScreenUpdating = False
    For Each x In Arr
    With RngIntersect
        .Replace What:=x, Replacement:="", LookAt:=xlPart
            End With
                Next x
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

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