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]

Apologies, a typo:

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

 

[Mike Welch]

Hmmm, made the screen blink but nothing disappeared ... no error messages either ...

Maybe ...

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

 

[Mike Welch]

Ohhhh! This is so close! It gets rid of all the line numbers in AC ... I wanted to keep the lines associated with the errors but get rid of everthing (in AC and AD) after the last error in the column AD. (I may not have explained that as clearly as I had intended, sorry.)

Apologies, a typo:

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

 

[shg]

made the screen blink but nothing disappeared

From your explanation, cells in column AD contain either some visible value, or a null string resulting from some prior formula.

The code turns the null strings into Empty values, effectively clearing them, which is what I understood you wanted.

 

[Mike Welch]

Yes, you understand correctly. I didn't see a change because the column "looks" empty to my eyes but I've since seen that this works great! It's not necessary (more for asthetics) but I was hoping to eliminate all the row numbers along side the non-errors (which your code converts into Empty values) in column AC. In the end, I'd have row numbers in AC matched to errors in AD and all cells below them would be Empty.

From your explanation, cells in column AD contain either some visible value, or a null string resulting from some prior formula.

The code turns the null strings into Empty values, effectively clearing them, which is what I understood you wanted.

 

[shg]

Sub y()
    With Intersect(Columns("AD"), ActiveSheet.UsedRange)
        .Value = .Value
        .SpecialCells(xlCellTypeBlanks).Offset(, -1).ClearContents
    End With
End Sub

 

[Mike Welch]

Wigi - thank you - this is great!

Apologies, a typo:

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

 

[Mike Welch]

Thank you shg! This tiny slice of code does exactly what I want! If I had ever solved it myself (after days if not weeks) I would have ended up with probably 5000 lines of code.

It's nice to have experts on here to rely on!!!

Mike

Sub y()
    With Intersect(Columns("AD"), ActiveSheet.UsedRange)
        .Value = .Value
        .SpecialCells(xlCellTypeBlanks).Offset(, -1).ClearContents
    End With
End Sub

 

[shg]

You're welcome, glad it worked for you.