A
akemeny
Is there a way to modify the macro below so that it only looks at the cells
that have actually been changed? Rather than having it look at the specific
columns when something on the spreadsheet has been changed.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim MyRange As Range
Dim c As Range
For Each wS In Worksheets
If wS.Range("C1").Value = "changed" Then
lastrow = Cells(Cells.Rows.Count, "AO").End(xlUp).Row
lastrow1 = Cells(Cells.Rows.Count, "AQ").End(xlUp).Row
lastrow2 = Cells(Cells.Rows.Count, "AY").End(xlUp).Row
lastrow3 = Cells(Cells.Rows.Count, "AZ").End(xlUp).Row
lastrow4 = Cells(Cells.Rows.Count, "BF").End(xlUp).Row
lastrow5 = Cells(Cells.Rows.Count, "BG").End(xlUp).Row
lastrow6 = Cells(Cells.Rows.Count, "BL").End(xlUp).Row
Set MyRange = Range("X1:BL" & WorksheetFunction.Max(lastrow, lastrow1,
lastrow2, _
lastrow3, lastrow4, lastrow5, lastrow6))
For Each c In MyRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
End If
Next wS
Thanks in advance for any help.
that have actually been changed? Rather than having it look at the specific
columns when something on the spreadsheet has been changed.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim MyRange As Range
Dim c As Range
For Each wS In Worksheets
If wS.Range("C1").Value = "changed" Then
lastrow = Cells(Cells.Rows.Count, "AO").End(xlUp).Row
lastrow1 = Cells(Cells.Rows.Count, "AQ").End(xlUp).Row
lastrow2 = Cells(Cells.Rows.Count, "AY").End(xlUp).Row
lastrow3 = Cells(Cells.Rows.Count, "AZ").End(xlUp).Row
lastrow4 = Cells(Cells.Rows.Count, "BF").End(xlUp).Row
lastrow5 = Cells(Cells.Rows.Count, "BG").End(xlUp).Row
lastrow6 = Cells(Cells.Rows.Count, "BL").End(xlUp).Row
Set MyRange = Range("X1:BL" & WorksheetFunction.Max(lastrow, lastrow1,
lastrow2, _
lastrow3, lastrow4, lastrow5, lastrow6))
For Each c In MyRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
End If
Next wS
Thanks in advance for any help.