CODEWITHSUNDEEP
excelvbarow
joehua
joehua

Reputation: 735

Excel VBA: End(xlUp) and End(xlDown) all end up at row 244, which is blank?

I filled B2:GQ244 with formulae, copied the range and pasted by value before sorting the range column by column. The cells in B8:GQ244 were all blanks. Then, I wanted to concatenate the non-blank cells column by column, starting from row 2. To do so, I needed to find the last non-blank cell in each column.

For some reason, both End(xlUp) and End(xlDown) gave row 244, which was empty. I can't figure out why. I thought the file might be corrupted. So, I copied the two sheets and the module to a newly created workbook to no avail. Any explanation why both End(xlUp) and End(xlDown) gave row 244?

.Range("B2:GQ244").Formula = "=IF(ISERROR(FIND( B$1,Sheet9!$H34)),"""",Sheet9!$I34)"

'paste by value to get rid of formulae

.Range("B2:GQ244").Copy
.Range("B2").PasteSpecial Paste:=xlPasteValues

'sort by column

Dim last_row As Long
Dim j As Long

For i = 2 To 200 Step 1

Range(.Cells(2, i), .Cells(245, i)).Sort key1:=.Cells(2, i), order1:=xlAscending

Next i

For i = 2 To 200 Step 1

last_row = .Cells(65536, i).End(xlUp).Row
last_row = .Cells(1, i).End(xlDown).Row

Upvotes: 0

Views: 3595

Answers (2)

Variatus
Variatus

Reputation: 14373

The code below will remove all null strings at the bottom of columns as well as those that contain zeroes.

Sub ClearBlankCells()
    ' 146
    
    Dim Rng         As Range            ' working range
    Dim R           As Long             ' intermediate: row
    Dim C           As Long             ' loop counter: columns

    Application.ScreenUpdating = False
    With ActiveSheet
        With .Range("B2:GQ244")
            .Formula = "=IF(ISERROR(FIND( B$1,Sheet9!$H34)),"""",Sheet9!$I34)"
            ' replace formulas with their values
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With
        Application.CutCopyMode = False

        For C = 2 To 200 Step 1
            Set Rng = .Columns(C)
            R = Application.Evaluate("SUMPRODUCT((" & Rng.Address & "<>"""")*(" & _
                                                      Rng.Address & "<>0)*1)")
            If R > 0 Then
                Set Rng = Range(.Cells(R + 1, C), .Cells(Rows.Count, C))
                Rng.ClearContents
            End If
            ' sort by column
'            Range(.Cells(2, C), .Cells(245, C)).Sort Key1:=.Cells(2, C), Order1:=xlAscending
        Next C
    End With
    Application.ScreenUpdating = True
End Sub

Note that no blanks or zeroes may be included in the block of data above the bottom of each column, including the caption.

Sorting must be done after such cells have been removed but I left the sort instructions dimmed out because it's wrong either in syntax or by concept. If you need to sort each column the syntax is wrong because the syntax sorts the entire sheet. On the other hand, if you want to sort the entire sheet you don't have to do it in a loop 200 times.

The code runs very slowly which gives rise to two observations.

  1. It spends 99% of its time repairing the damage it has done in its first line.
  2. It looks at a data range which is vastly bigger than what is actually, reasonably, required. Nobody wants to look at a sheet 200 columns and 244 rows.

Therefore there must be much better ways to do achieve what you want.

Upvotes: 1

Variatus
Variatus

Reputation: 14373

I can't confirm your findings. Having a blank ActiveSheet and a blank Sheet9 the code below filled the ActiveSheet with zeroes B2:GQ244. It then read the last row xlUp as 244 and xlDown as 2. Both of these values are as expected. Perhaps you have a setting that suppresses the display of zeroes. However, as explained in my comment above, a cell that appears blank isn't necessarily blank and that would also apply to a cell containing a NullString inserted by your formula, even if the formula was subsequently removed leaving the null string in its place.

Sub Examine()
    
    Dim last_row As Long
    Dim i As Long

    With ActiveSheet
        .Range("B2:GQ244").Formula = "=IF(ISERROR(FIND( B$1,Sheet9!$H34)),"""",Sheet9!$I34)"
        
        'paste by value to get rid of formulae
        
        .Range("B2:GQ244").Copy
        .Range("B2").PasteSpecial Paste:=xlPasteValues
        
        'sort by column
        
        For i = 2 To 200 Step 1
            Range(.Cells(2, i), .Cells(245, i)).Sort Key1:=.Cells(2, i), Order1:=xlAscending
            last_row = .Cells(.Rows.Count, i).End(xlUp).Row
            Debug.Print last_row            ' returns 244
            last_row = .Cells(1, i).End(xlDown).Row
            Debug.Print last_row            ' returns 2
        Next i
    End With
End Sub

The only mystery remaining, therefore, is why .Cells(1, i).End(xlDown).Row gives you a value of 244. It doesn't. Therefore the solution must be in the conduct of your test, not in its result. Compare your testing method with the one I employed above.

Upvotes: 0

Related Questions