Locating last non-blank cell in sorted column

[Mike Welch]

Hi - I have two columns in a spreadsheet , AC and AD. AD contains error messages or a blank ("") based on an earlier function. AC is the row number that corresponds with a given error or blank.

I then sort both columns by AD (descending) and all the errors come to the top with their associated row numbers. Below the errors come all the blank lines. I know that NumberOfRows = Range("AD65536").End(xlUp).Row will find the last row but it includes the blank rows (currently identifying row 6097 even though I only have 37 errors).

I'd like to delete everything in both AC and AD after the last error so I could sort by row number making it easier to quickly locate errors for repair. Any thoughts?

Mike / WinXP Pro / Excel 2003

 

[wigi]

For instance:

Sub fff()
    Columns("AD").SpecialCells(xlCellTypeFormulas, xlTextValues).Cells(1).Offset(, -1).Resize(ActiveSheet.UsedRange.Rows.Count).ClearContents
End Sub

Since the code clears cells, please test it on a BACKUP of your actual file.

 

[Mike Welch]

wigi -

I pasted that in as a separate piece of code and ran it. It returns with Run-time error '1004': No cells were found. Pressing Debug just highlights the entire line (which isn't too helpful IMHO). I clearly see data in columns AC and AD. Any idea what happened?

For instance:

Sub fff()
    Columns("AD").SpecialCells(xlCellTypeFormulas, xlTextValues).Cells(1).Offset(, -1).Resize(ActiveSheet.UsedRange.Rows.Count).ClearContents
End Sub

Since the code clears cells, please test it on a BACKUP of your actual file.

 

[wigi]

Do you really have blank cells in column D, or is it "" as the result of a formula?

 

[Mike Welch]

They look blank ... I see nothing, they have no space hidden in there, doing LEN(cell) comes up with zero ...

The content of column AD is a copy from column V where a Paste Special of Values is used. Column V is a formula: =IF(AND(B71=1,S71<>40),M71,"")

Do you really have blank cells in column D, or is it "" as the result of a formula?

 

[wigi]

You did not tell me that you have no formulas in column AD. So, try on a copy of the file:

Sub fff()
    With ActiveSheet.UsedRange.Rows
        Columns("AD2:AD" & .Count).SpecialCells(xlCellTypeConstants, xlTextValues).Cells(1).Offset(, -1).Resize(.Count).ClearContents
    End With
End Sub

 

[Mike Welch]

Interesting ... apparently it's not blank. I placed in an adjacent cell =CELL( "type", AD66 ) and I get back an "l" (no quotes) indicating a text constant.

They look blank ... I see nothing, they have no space hidden in there, doing LEN(cell) comes up with zero ...

The content of column AD is a copy from column V where a Paste Special of Values is used. Column V is a formula: =IF(AND(B71=1,S71<>40),M71,"")

I tried the second solution you provided (thank you!) but got a Run-time error '1004' / Application defined or object defined error

 

[wigi]

Then my code should work (although I did not test it explicitly since I do not have the exact information on cell contents).

 

[Mike Welch]

I re-checked, closed then reopened the workbook, and still end up getting Run-time error "1004" - Application defined or object defined error. Same number but different message ... ugh!

Then my code should work (although I did not test it explicitly since I do not have the exact information on cell contents).

 

[shg]

Maybe ...

Sub x()
    With Intersect(Columns("AD"), ActiveSheet.UsedRange)
        .Value = .Value
    End With
End Sub