<div>
Change the for loops to :<br />
<br />
&nbsp; &nbsp; 'For Each objCell In Range(&quot;E:E&quot;)<br />
&nbsp; &nbsp; For Each objCell In Range(&quot;E2:E&quot; &amp;&nbsp;Range(&quot;E65536&quot;).End(xlUp).<wbr />Row)<br />
&nbsp; &nbsp; &nbsp; &nbsp; If objCell.Value &lt;&gt;&nbsp;&quot;&quot; Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ConvertDate objCell<br />
&nbsp; &nbsp; &nbsp; &nbsp; End If<br />
&nbsp; &nbsp; Next<br />
&nbsp; &nbsp; <br />
&nbsp; &nbsp; 'For Each objCell In Range(&quot;F:F&quot;)<br />
&nbsp; &nbsp; For Each objCell In Range(&quot;F2:F&quot; &amp;&nbsp;Range(&quot;F65536&quot;).End(xlUp).<wbr />Row)<br />
&nbsp; &nbsp; &nbsp; &nbsp; If objCell.Value &lt;&gt;&nbsp;&quot;&quot; Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ConvertDate objCell<br />
&nbsp; &nbsp; &nbsp; &nbsp; End If<br />
&nbsp; &nbsp; Next<br />
<br />
<br />
'Do the others in the same way<br />

</div>


<div>
From what I see, these are working except that:<br />
<br />
1) timezone CET is undefined<br />
2) the loops stop working when they find a blank row, even if there's data after it.<br />
<br />
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.<br />
<br />
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 &nbsp;column F, but you'll need to confirm this. &nbsp;If so, the condition to exit could be adjusted to accommodate that.
</div>


<div>
For example, you could change this line in each loop:<br />
<br />
&nbsp; &nbsp; &nbsp; &nbsp; If objCell.Value &lt;&gt;&nbsp;&quot;&quot; Then<br />
<br />
to this:<br />
<br />
&nbsp; &nbsp; &nbsp; &nbsp; If Cells(objCell.Row, 6).Value = &quot;&quot;&nbsp;Then<br />

</div>


<div>
Range(&quot;E65536&quot;).End(xlUp).<wbr />Row returns row ID of the last used cell in column &quot;E&quot;.<br />
So, loop for column E (in this particular case) will be equivalent to :<br />
<br />
&nbsp; &nbsp; For Each objCell In Range(&quot;E2:E160&quot;)<br />

</div>


<div>
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?<br />
<br />
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,<br />
1) the macro i have now needs to also work for column E<br />
2) current macro does not convert all the columns properly. Please try and you will see that there are problems in &nbsp;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. 
</div>


Updated-Copy-of-test-Template-fi.xls

<div>
Sorry S/B...I missed the part about End(xlUp)...that is indeed a good solution here.<br />
<br />
teaone, can you please indicate a few cells where it does not convert correctly? &nbsp;when I ran it, the dates converted correctly.<br />
Show the cell #, what is starts out with and how it ends up.<br />
<br />
Is it possible that you are using a certain date format that maybe VBA does not recognize? &nbsp;I suspect your system set to Standard US, so macro may be interpreting things incorrectly in some cases.<br />
Also, you should move the &quot;Debug.print&quot; line up one row so it shows the before/after in the output window.
</div>


<div>
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. <br />
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.)<br />
<br />
If one of you could please fix the macro to ensure:<br />
1) blank cells are not stopping the code from running down all the rows that have data<br />
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!<br />
3) the macro allows to enter additional timezones (i can do that later) as needed<br />
&nbsp;
</div>


<div>
never mind my last comment. retesting
</div>


<div>
Okay, Santababy, seems to work now. Two issues:<br />
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?<br />
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? <br />
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. <br />
<br />
Thanks
</div>


<div>
Some thoughts for how to address your concerns:<br />
<br />
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.<br />
2) when processing, if the data in the cell is already a date, don't convert<br />
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.
</div>


<div>
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.
</div>


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


<div>
<div class="content wysiwyg-content">
Please see the attached file that contains the problem definition and macro to be fixed. Please keep in mind that this will be used as a template and new data will &nbsp;be pasted in every time. So, I need this macro to work every time I paste new data in.<br />
<a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/460498/Copy-of-test-Template-fix-1-.xls" target="_blank" class="file-inline" title="Test file (132 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Copy-of-test-Template-fix-1-.xls</a>
</div>
</div>


Copy-of-test-Template-fix-1-.xls

Please see the attached file that contains the problem definition and macro to be fixed. Please keep in mind that this will be used as a template and new data will  be pasted in every time. So, I need this macro to work every time I paste new data in.

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.