Trouble with selecting last non blank row in reference

Question

I asked the question yesterday but the answers could not solve it... Selecting a range until at least one column has numeric value

The problem is:

I have a range of some columns and some rows which is a reference, so the formula is like this for that range (that consists of 118 rows and 7 columns):

{IFERROR(HLOOKUP(Data!D371:D478;Data!B361:HU367;4;FALSKT);"NA")}

I then copy that range (where only 60 rows are non-blank and the rest of the rows are blank cells) to the other range with:

=IF(ChosenData!B196="NA";"";ChosenData!B196)

and so on...

I then use the VBA to copy it over to an other sheet in my workbook:

Sub GetRet()
    Worksheets("ChosenData").Select
    Range("J195").CurrentRegion.Select
    Selection.Copy

    Worksheets("Analys").Select
    Range("L10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

The sub above selects all the 118 rows but I only want to select only the rows until the row where at least one of the columns is non-blank.

Last post suggested to use:

Lastrow = Evaluate("=MATCH("""",L10:L1000&M10:M1000&N10:N1000&O1:O1000&P10:P1000&Q10:Q1000&R10:R1000,0)")

But that solution select some randow row (which is non-blank) !

Best Regards!

Does anyone have a suggestion?

loop througnt rows: `For i=10 to 1000` and check whether all columns you need are blank. If so - rememeber `i` - it's your `lastrow` and `exit for` — Dmitry Pavliv, Mar 27 '14 at 08:57
@simoco I am new to Excel, come from R... Could you please post an answer, a sub? Best Regards:) — user1665355, Mar 27 '14 at 08:59

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

Try this one:

Sub test()
    Dim lastrow As Long
    Dim i As Long

    With Worksheets("ChosenData")
        For i = 10 To 1000
            'compare concatenation of columns L:Q with empty string
            If Trim(Join(Application.Transpose(Application.Transpose(.Range("L" & i & ":Q" & i).Value)), "")) = "" Then
                lastrow = i
                Exit For
            End If
        Next
    End With

    MsgBox "Lastrow is:" & lastrow
End Sub

if you're interesting how Join works in this example, look into @Tim Williams answer here.

Futher improvment, you can change 1000 in loop to last row of entire sheet

edited May 23 '17 at 12:28

Community

1
1

answered Mar 27 '14 at 09:11

Dmitry Pavliv

35,333
13
79
80

@simoco... I run that sub but it does not return anything:/ Not MsgBox at least... – user1665355 Mar 27 '14 at 09:19
sorry, I've updated my code, try now (there should be `Exit For` instead `Exit Sub` in loop) – Dmitry Pavliv Mar 27 '14 at 09:20
Yeah great! Very good answer! Trying now to get that row so I select `L10:R67` correctly:P – user1665355 Mar 27 '14 at 09:23
Something like that, but that seem wrong `Range("L10" &":" &"M & ":" & lastrow).Select` – user1665355 Mar 27 '14 at 09:25
1

that works `Range("L10:M" & lastrow - 1).Select` . Thanks you are the best:D – user1665355 Mar 27 '14 at 09:30

Trouble with selecting last non blank row in reference

1 Answers1