Excel: help to fix macro that converts dates from local timezones to GMT

Change the for loops to :

    'For Each objCell In Range("E:E")
    For Each objCell In Range("E2:E" & Range("E65536").End(xlUp).Row)
        If objCell.Value <> "" Then
            ConvertDate objCell
        End If
    Next
   
    'For Each objCell In Range("F:F")
    For Each objCell In Range("F2:F" & Range("F65536").End(xlUp).Row)
        If objCell.Value <> "" Then
            ConvertDate objCell
        End If
    Next


'Do the others in the same way

From what I see, these are working except that:

1) timezone CET is undefined
2) the loops stop working when they find a blank row, even if there's data after it.

SANTABABY's solution will help, but it will make the processing very slow since it will go through 65K entries per column instead of the 162 rows that are in the attached workbook.

At this point, I think a better solution is to determine something that helps indicate where the last row is located, which at this point seems to be  column F, but you'll need to confirm this.  If so, the condition to exit could be adjusted to accommodate that.

For example, you could change this line in each loop:

        If objCell.Value <> "" Then

to this:

        If Cells(objCell.Row, 6).Value = "" Then

Range("E65536").End(xlUp).Row returns row ID of the last used cell in column "E".
So, loop for column E (in this particular case) will be equivalent to :

    For Each objCell In Range("E2:E160")

guys, i am sorry, i am not that good with any sorts of codes. can you please update the provided file to make it work?

Again, I will be pasting new data into this template each time. It will have various timezone indicators in the specified columns. I can update all the timezones I need in the macro. It's not a problem. However,
1) the macro i have now needs to also work for column E
2) current macro does not convert all the columns properly. Please try and you will see that there are problems in  columns K and L. Not only the code stops working once stumbles upon an empty cell but it also does not convert right - outputs some strange dates. For blank cells, it's okay if it outputs something like None or Blank or Error or N/A but it has to continue down for the rest of the data.

Sorry S/B...I missed the part about End(xlUp)...that is indeed a good solution here.

teaone, can you please indicate a few cells where it does not convert correctly?  when I ran it, the dates converted correctly.
Show the cell #, what is starts out with and how it ends up.

Is it possible that you are using a certain date format that maybe VBA does not recognize?  I suspect your system set to Standard US, so macro may be interpreting things incorrectly in some cases.
Also, you should move the "Debug.print" line up one row so it shows the before/after in the output window.

I just noticed another issue: the converted data gets entered into the columns next to the date/time columns erasing the contents! For example, the column named Duration now has the date/time in it.
Can these converted dates be entered into any other columns, like at the end of the file, for example? Also, note that I have special columns already prepared for these but the original macro does not account for that. The special columns ready to accept the converted data are columns CK through CN (forgot to add one more as we are converting 5 columns.)

If one of you could please fix the macro to ensure:
1) blank cells are not stopping the code from running down all the rows that have data
2) converted data either remains in the same column/cell or moves to the end of the file or dedicated columns but does not erase data from any other columns!
3) the macro allows to enter additional timezones (i can do that later) as needed
 

never mind my last comment. retesting

Okay, Santababy, seems to work now. Two issues:
1. I need to know when there's an invalid timezone so that I know to fix that and not use the wrong data. Can we throw and error in those cells instead of leaving them with wrong time stamps?
2. This is a big one: Everything works fine when I click Fix Dates button once. But If I accidentally click it again, it does some weird recalculation and the dates end up being off. How can we prevent this from happening?
3. Is there a rollback option that you build (similar to Undo) - if the user clicks Fix Data and then has doubts, they should be able to roll back and review their data and then go ahead and click Fix Data again.

Thanks

Some thoughts for how to address your concerns:

1) If an invalid code is found, skip the processing and highlight the background of the cell in red (although it may be hard to find with large amounts of data.
2) when processing, if the data in the cell is already a date, don't convert
3) rollback would be impractical with this much data; a better choice is to put the results into a different place, such as a new sheet...maybe have the code make a copy of the sheet and run it against the copy so you have the original to examine.

Can you help accomplish this with the code? I would prefer to keep in the same sheet if we can highlight the ones that did not convert - data kept as is and cell is highlighted. And yes, if date has already been converted, do not process again.

Working....

Santababy, i will close this out as successful and submit a few more separate questions. Thank you.