Could someone please examine this could code and advise if it could be rewritten more efficiently?
I've been using this same macro for over a year now but lately it's been taking much longer to run it's course as the number of records grows. The spreadsheet I'm using this macro is not really a huge worksheet of records (currently up to 480 rows). I also have several different worksheets for different purposes where I use a similar macro (each has a diff sub name to call with a different named range) and have a button setup on the main worksheet that when clicked, calls all 7 of these macros. This macro works just fine, however altogether to run through all of them is taking sometimes up to nearly 15 minutes.
Basically the purpose of this macro is to look down a certain column and if a cell is blank, I want it to copy the date from a previous column (for this particular macro, the date I want to copy is 6 rows back). I have the specific range of cells named with an offset formula with in the Names Manager, so refer to the column in the code with the name of the range instead (range("updated_Date")).
I do have quite a bit of records where I don't have a deadline date in either column, so am thinking that is in part what slows down this macro because it is essentially copying a blank value over to the destination column (range) but I don't know how to modify this code to account for this.
Below is an example of one of these macros. All 7 are pretty much the same except on some, the offset is only back one row or I resize the range, etc (e.g.: icell.Resize(1, 2).Value = icell.Resize(1, 2).Offset(0, -2).Value).
Any help would be greatly appreciated! Thanks!
I've been using this same macro for over a year now but lately it's been taking much longer to run it's course as the number of records grows. The spreadsheet I'm using this macro is not really a huge worksheet of records (currently up to 480 rows). I also have several different worksheets for different purposes where I use a similar macro (each has a diff sub name to call with a different named range) and have a button setup on the main worksheet that when clicked, calls all 7 of these macros. This macro works just fine, however altogether to run through all of them is taking sometimes up to nearly 15 minutes.
Basically the purpose of this macro is to look down a certain column and if a cell is blank, I want it to copy the date from a previous column (for this particular macro, the date I want to copy is 6 rows back). I have the specific range of cells named with an offset formula with in the Names Manager, so refer to the column in the code with the name of the range instead (range("updated_Date")).
I do have quite a bit of records where I don't have a deadline date in either column, so am thinking that is in part what slows down this macro because it is essentially copying a blank value over to the destination column (range) but I don't know how to modify this code to account for this.
Below is an example of one of these macros. All 7 are pretty much the same except on some, the offset is only back one row or I resize the range, etc (e.g.: icell.Resize(1, 2).Value = icell.Resize(1, 2).Offset(0, -2).Value).
Any help would be greatly appreciated! Thanks!
Code:
Sub Merge_Date_Columns()
Dim icell As Range
Application.ScreenUpdating = False
Sheets("Compliance").Select
For Each icell In Range("Updated_Date")
If icell.Value = "" Then
icell.Value = icell.Offset(0, -6).Value
End If
Next icell
Application.ScreenUpdating = True
MsgBox "Macro Complete"
& vbLf _
& "Please press OK to continue."
End Sub
