Problem with "Do Until IsEmpty(ActiveCell)"

[Snort]

I created a macro that, among other things, cycles through the column headings standardizing the heading of relevant columns. I loop through the columns by

 Range("A1").Select

Do Until IsEmpty(ActiveCell)

    blah blah blah
    
    ActiveCell(1, 2).Select
    
Loop

This works great with no problems as far as I can tell on most sheets I run it on, but every now and then there are times when it goes all the way to column XFD (which causes problems with the rest of the macro) and I can't figure out why.

• I'll hit Control + End on the problem sheet and it'll show the last cell is in column AY for example. Run the macro, and Control+End now brings me to column XFD.
• I go one column over from wherever Control + End brings me on the problem sheet, use the immediate window and get a TRUE result from "debug.print IsEmpty(ActiveCell)", yet the macro still runs to column XFD
• I've tried using ASAP Utilities to "Reset Excel's Last Cell" and still get the same result on the problem sheet. Column XFD.

Any idea's on what's causing this on some sheets but not most?
Do you have a better method to loop through the populated cells in Row 1? The number of columns changes per sheet based on the source of the data.

Thanks for your help!

 

[Joe4]

Welcome to the Board!

Yes, there is a better way to loop through all the populated cells in row 1.
Note that if there is anything in the body of code changing your active cell, that code will not function as you desire.

As long as cell XFD1 is not populated, you can use something like:

Dim lc as Long
Dim c as Long

'Find last populated column in row 1
lc = Cells(1, Columns.Count).End(xlToLeft).Column

'Loop through row 1 up to the last column
For c = 1 to lc
'   blah blah blah
Next c

Within that For Loop, you can reference the current cell/column like this:

Cells(1, c)

Note that it is seldom necessary to actually select the cells in order to work with them. As matter as fact, using "ActiveCell" and "Select" or "Activate", especially in a loop, with unnecessarily affect your performance and slow down your code. So avoid it whenever possible.

 

[Snort]

Fantastic - thanks for the info! I'll give this a shot and update a few other macro's that use the same method of looping through the column headings.

 

[Joe4]

Sounds good.
Feel free to to post back with any questions.

 

[Snort]

The for loop for the columns works as expected, but the extra column problem is still happening. Certain sheets still push out the last column to XFD. I tried copy/paste the data into a new workbook and had the same results (column XFD).

I just added a msgbox before the line that always errors on these problem sheets, and it shows the number of columns is 46, which is accurate for this sheet. The very next line, which is where the errors occur, is

Columns("B:B").Insert shift:=xlToRight

Is there a better way to add an empty column?

 

[Snort]

Curious - I just put a breakpoint on that problem line and ran the macro up to that point. Worked fine as far as I could tell. Attempted to manually insert the column (right click > insert), and had the same problem.

I'm stumped.

 

[Joe4]

Please post your entire code and any error messages that you are getting.
Are you applying any formatting, formulas, or anything to the entire sheet or entire rows?

 

[Snort]

Found the problem finally - trying to trim the entire row.

'*******Remove Excess Spaces****************
Dim RNG As Range

Rows(1).Select

Set RNG = Selection
RNG.Value = Application.Trim(RNG)
'*********************************************

Works fine when that is commented out, so this problem is solved.

My next question for you is that this macro works by creating a different dictionary for every relevant column I want standardized, then checking each column heading against those dictionaries. Wondering if there's a better way. Here's a snippet of my code. I capped it at 5 max entries per dictionary for this post, but there's many more in reality.

'**********************************************
Dim AgencyName As New Scripting.Dictionary
AgencyName.CompareMode = TextCompare

AgencyName.Add "ProvName", 1
AgencyName.Add "FACIL_NM", 1
AgencyName.Add "Svc Prov Org Name", 1
AgencyName.Add "Serv Group Practice Name", 1
AgencyName.Add "Billing Prov LName", 1
'***********************************************
'*********************************************
Dim AgencyProviderNumber As New Scripting.Dictionary
AgencyProviderNumber.CompareMode = TextCompare

AgencyProviderNumber.Add "PayToProviderNumber", 1
AgencyProviderNumber.Add "clinic_npi", 1

'**********************************************
'**********************************************
Dim BilledAmount As New Scripting.Dictionary
BilledAmount.CompareMode = TextCompare

BilledAmount.Add "SUBMITTED", 1
BilledAmount.Add "Submitted Charge", 1
BilledAmount.Add "billed_amt", 1
BilledAmount.Add "Billed Amt", 1
BilledAmount.Add "BILLED AMOUNT", 1
'***********************************************

Range("A1").Select

For X = 1 To LastColumn

    Z = Cells(1, X)
        
    If xRecipID.Exists(Z) Then
        ActiveCell.Value = "Recipient ID"
            
    ElseIf AgencyName.Exists(Z) Then
        ActiveCell.Value = "Agency Name"
        
    ElseIf AgencyProviderNumber.Exists(Z) Then
        ActiveCell.Value = "Agency Provider Number"
        
    ElseIf AgencyType.Exists(Z) Then
        ActiveCell.Value = "Agency Type"
        
    ElseIf BilledAmount.Exists(Z) Then
        ActiveCell.Value = "Billed Amount"
        
    End If
Next X

Many more dictionaries and entries in said dictionaries in the rest of the code. This way has been working fine for my needs, but I've often wondered if there's a better / different way to do this, but I haven't yet found the right question to ask on Google to find this answer

 

[Joe4]

Found the problem finally - trying to trim the entire row.

Seeing as how you had not mentioned or posted that anywhere in this question until, there was absolutely no way we could have helped you with that.
That is why it is important to post ALL your code in that procedure.

My next question for you is that this macro works by creating a different dictionary for every relevant column I want standardized, then checking each column heading against those dictionaries. Wondering if there's a better way. Here's a snippet of my code. I capped it at 5 max entries per dictionary for this post, but there's many more in reality.

As that is a brand new/different question, it should be posted in a brand new thread.
I have very little experience using dictionaries, so I don't think I could much assistance on that issue anyhow.

 

[Snort]

I understand what you're saying about posting the entire code; this macro is more than 20 pages long when pasted into Word and I thought that would be a bit excessive and potentially taboo to post it all. I will post it all next time I need help debugging.

Thanks again for your help! Have a great day!