excel vba - VBA: Replacing space bars with blank spaces? -

i running code pulls data pi using conditional formula. when values pulled, assigns formula entire column few of them have actual values:

the column has 300,000 rows want run loop when have actual values (4 times instead of 300,000 in case).

are there suggestions avoiding loop 300,000 times?

i have tried using replace function replacing spaces blank , count number of non-blank cells using counta :

'replacing spaces in column blanks workseets("sheet6").range("d:d") = replace(worksheets("sheet6").range("d:d")," ","") 'counting non-blank cells n = worksheetfunction.counta(worksheets("sheet6").range("d:d")) 'running code 4 times = 1 n..... 

but getting type mismatch error replace function. have not written inside for loop yet. trying use replace function correctly

your attempt uses string.replace function , causes mismatch error, because you're passing variant/range function expects string.

'replacing spaces in column blanks workseets("sheet6").range("d:d") = replace(worksheets("sheet6").range("d:d")," ","") 

a solution use range.replace method, instead. no assignment necessary, method operates on range object calls it. copies values directly , replaces empty string

'replacing spaces in column blanks  worksheets("sheet6").range("d:d")     .value = .value2     .replace " ", "", xlwhole  end