CODEWITHSUNDEEP
excelvba
kd99
kd99

Reputation: 65

How to use Rows.Count function if there are blank cells in between data

I am trying to write a code that adds in data from my excel sheet if the item the user selects is equal to the range in J. This works perfectly if the range in J is filled in with all the data, but how do I get the row to still count all the way through the last filled cell if there are blanks in between? I attached a picture to show what I mean.

enter image description here.

I would want to count the rows all the way down to the last "Gold". Right now it only counts to the second. 

Private Sub cboName_Click() 'only get values that are assigned
    Dim j As Integer, k As Integer, i As Integer
    Me.lstProvider.Clear
    i = 0
    Worksheets("Biopsy Log").Select
    For j = 1 To Range("J2", Range("J1").End(xlDown)).Rows.count 
        If Range("J2", Range("J2").End(xlDown)).Cells(j) = Me.cboName.Value Then 
            If Range("C2", Range("C2").End(xlDown)).Cells(j) = "Assigned" Then 
            With Me.lstProvider
                .AddItem
                For k = 0 To 5
                    .List(i, k) = Range("A" & j + 1).Offset(0, k) 
                Next
            End With
            i = i + 1 
            End If
        End If
    Next
End Sub

Upvotes: 3

Views: 2139

Answers (2)

Osman Wong
Osman Wong

Reputation: 170

Instead of using xlDown, try to use xlUp from the bottom to get the last row for correct range:

Dim sht As Worksheet
Set sht = Worksheets("Biopsy Log")

For j = 1 To sht.Range("J" & sht.Rows.Count).End(xlUp).Row
    If sht.Range(...)

Qualifying Range calls with an explicit Worksheet object makes your code more robust.

Upvotes: 1

Michal
Michal

Reputation: 5848

Instead of For j = 1 To Range("J2", Range("J1").End(xlDown)).Rows.count use Range("J" & Rows.Count).End(xlUp).Row (assuming GOLD is in column J). The code does the opposite of xlDown. It goes down to the last row of the sheet (Rows.count) and moves up until it find the first non-blank cell.

Upvotes: 1

Related Questions