Macro Help
 

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.

Macro Help
 

Maybe use a Worksheet_Change event instead of the BeforeClose event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target Is Nothing and Not Target.HasFormula Then
Target.Value = Trim(Target.Value)
End If
End Sub

This would monitor the changes as they occur and immediately apply fix.


"akemeny" wrote:

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.