xldown counting blank string cells from formulas

[szymagic]

Hello,

My problem is that I have an xldown finding the last row in my data, however it drops down to the bottom of a formula I have in place. This formula will returns blanks, and I'd like xldown to ignore these blanks. So the column looks like this:

.......A
1.....10
2.....82
3.....43
4.....30
5.....""
6.....""
7.....""

I want the cell A4 not A7.
I apologize if there is already a thread on this matter, but I couldn't find any.
Thanks a bunch!

 

[sheetspread]

lr = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row

excludes the ""s

 

[JeanKim]

question 1: "" <---does this represent blank cell?
question 2: post code you have already

comment: if you use xldown you will be not skip blank rows so suggest use xlup instead of xldown

edit: my bad. misunderstood your question.

 

[szymagic]

Windows("Priority Calculator.xlsm").Activate
    Range("AO85:AO285").Select
    Selection.Copy
Windows("GIS_Table.csv").Activate
    Range("A2").Select
    ActiveCell.End(xlDown).Select
    lastrow = ActiveCell.Row
    Cells(lastrow + 1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Windows("Priority Calculator.xlsm").Activate
    Range("AO83").Select
    Selection.Copy
Windows("GIS_Table.csv").Activate
    Range("A2").Select
    ActiveCell.End(xlDown).Select
    x = ActiveCell.Row
    Range("B2").Select
    ActiveCell.End(xlDown).Select
    Range(Cells(lastrow + 1, 2), Cells(x, 2)).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Here it is, it is supposed to paste a large range of cells in column A, that could or could not have data in them. Then find the last row where there is data, and then paste the same value in column B in an array from column B's last row of data to the last row of data in column A.

Go easy on me, I am such a greenhorn at this haha,
Thanks for all your help

 

[szymagic]

lr = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row

excludes the ""s

Thanks! Works like a charm! I really appreciate it!

 

[Mike Szczesny]

Can you use something like this. This seems to work. Hope this helps.

Excel 2012

	A	B	C
""
" "
""

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=LOOKUP(2,1/ISNUMBER($A$1:$A$7),$A$1:$A$7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]