Excel 97 problem with Address Property/Cell References

[cjashwell]

Hi

Can somebody give me some assistance with the following:

I'm writing a routine to clean some data in an Excel 97 spreadsheet. I need to have each column 'cleaned' using the worksheet functions CLEAN and TRIM. The sub I've written goes through the worksheet inserting a new column next to each existing column, populating it with =CLEAN(TRIM(cell)), copying the resultant 'clean' data and then pasting the Values only into the original column, before deleting the formula filled column.

It works fine for the first column, but when it gets to the second column and hits line 13 of the code the cell addresses go awry. It starts to offset the active cell address by 1 column, so active cell $B$1 become $C$1.

I have no idea why this is happening and will be extremely grateful if someone can assist! My only suspicion is that it has something to do with the Address property - but I'm not sure what!

The code is here:

Sub clean_data()

Dim intRowCount As Integer, intColCount As Integer, intCounter As Integer
Dim MRange As range, rng1 As range, rng2 As range, rngNew As range
Dim strCell As String

1 intColCount = Application.WorksheetFunction.CountA(ActiveSheet.range("A1:AA1&quot)
2 intRowCount = Application.WorksheetFunction.CountA(ActiveSheet.Columns("A:A&quot)

3 Set rng1 = ActiveSheet.Cells(1, 1)
4 Set rng2 = ActiveSheet.Cells(intRowCount, intColCount)
5 Set MRange = ActiveSheet.range(rng1.Address, rng2.Address)

6 intCounter = 1

7 Do While intCounter < intColCount

8 MRange.Cells(, intCounter).EntireColumn.Select
9 Selection.EntireColumn.Insert
10 ActiveCell.Select

11 ActiveCell.Formula = &quot;=CLEAN(TRIM(&quot; & ActiveCell.Offset(0, 1).Address(RowAbsolute:=False, ColumnAbsolute:=True, ReferenceStyle:=xlA1) & &quot)&quot;
12 ActiveCell.range(ActiveCell.Address, ActiveCell.Offset(intRowCount, 0)).Select
13 ActiveCell.range(ActiveCell, ActiveCell.Offset(intRowCount)).FillDown
14 ActiveCell.range(ActiveCell, ActiveCell.Offset(intRowCount)).Copy
15 ActiveCell.Offset(0, 1).PasteSpecial (xlPasteValues)
16 Application.CutCopyMode = False
17 ActiveCell.Offset(0, -1).Select
18 Selection.EntireColumn.Delete

19 intCounter = intCounter + 1
20 Loop

End Sub

Please, please, please ... if someone can help it will stop me tearing my hair out.

Thanks in advance,
Corin