Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro looping through all Columns in a spreadsheet

0

Hello guys;

I have an issue with a file coming from a database, it contains some 'zero-length strings' and all the fields that are supposed to be 'blank' are not. My file has hundreds of thousands of rows and 135 columns, so using the method 'find & replace' for sometehing like '$$$$' just causes excel to crash and it never completes. 

I found online that if you do 'text to columns' ('Delimited' > uncheck all 'Delimiters' and select '{none}' as the 'Text qualifier') for each column it makes the problem go away. I tried manually with one column and it works, but I'm trying to build a macro to just repeat the 'text to columns' for each of the columns I have in the same sheet in my spreadsheet, but I just can't find a way online to build that macro, any ideas? 

I added a sample file, in row 4 I added a formula so you can see those columns where the zero-lenght strings exist. 

Thanks a lot for your help!

Luis

Answer
Discuss

Answers

0

I think the code below will serve your purposes well enough.

Sub ReplaceVbNullStrings()
    ' 003

    With ActiveSheet.UsedRange
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

Its drawback is that it resets all cell formatting. I recommend it in the belief that your sheet doesn't have any cell formatting as would be the case if you have a fresh, blank worksheet and copy data to it from a database.

Try it. If it turns out that you do need to preserve formatting you will require code to look at each cell one by one. 

Either way I don't recommend the way you found to be replicated in code. Your way creates conditions by keyboard strokes which cause Excel to take the action the effect of which you want. When code is used I would look for a more direct way, like the above procedure demonstrates.

Discuss

Discussion

Hello Variatus;

Thanks a lot for your response, unfortunately it didn't work, I used the same sample file I provided to run this macro and the zero-length srings remain there after running the macro. I tried doing the same manually for one of those cells and it didn't get rid of the problem. Any other ideas? Thanks!
zywll May 11, '20 at 5:25 pm
I down-loaded a fresh copy of your file. Ran the test the way I had done before and was surprised to find that the COUNTAs hadn't changed. Then I spent 20 minutes trying to find why it had worked before and wouldn't work now. The laugh's on me, my friend. The nulls are gone but so are the formulas. They were replaced by their values. That's why the cells still show the previous results. Please count again.
Variatus (rep: 4889) May 11, '20 at 8:42 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login