Code deleting values -- not as intended

[Stuart]

I pass to this sub:

Public Sub FormatWorksheetForAccess(ws As Worksheet)

Dim C As Range, rng As Range, j As Long

With ws
.Unprotect
.Columns("K:L").ColumnWidth = 5
'differentiate between the two % columns. Rename one.
.Range("L1").Value = "%ge"
'before deleting duplicate Field Name rows, place a marker in col A.
For Each C In .Range("A2", "A" & .Range("N65536").End(xlUp).Row)
If C.Value = "Item" Then
C.EntireRow.ClearContents
C.Value = "#"
etc.

It appears that if 'Item' is in "A1" then that row is deleted.
So my For Each statement is failing?

Any help much appreciated

 

[Bob Flanagan]

Stuart, how about doing it this way:

With ws
.Unprotect
.Columns("K:L").ColumnWidth = 5
'differentiate between the two % columns. Rename one.
.Range("L1").Value = "%ge"
'before deleting duplicate Field Name rows, place a marker in col A.
dim R as long
R = .Range("N65536").End(xlUp).Row
if R > 1 Then
For Each C In .Range("A2", "A" & R)
If C.Value = "Item" Then
C.EntireRow.ClearContents
C.Value = "#"
end if

The most likely reason row 1 is being cleared is that ther is no entry in
column N so the row number returned is row 1, and the for...Each is then
running on row 1, then row 2.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Stuart]

Yes you're right about the lack of values in col N (g).
I'm trying to create a test sheet, prior to importing
into Access. I had deleted all data in the sheet except
col 'O' and row 1, for the purpose of the test.
Then stupidly ran the original code on the new sheet.

Thanks for the help.

Regards.