Ignoring Blank Cells in a range

[BigH]

Hi There,

I have formulas going from a2:a1000, when the value (Part Number)equals zero
or N/A the cell remains blank. I then sort the data so that the values show
starting from a2 down to where ever.

I am looking for code that shows me the last cell with a value in it,
basically the cells maybe blank however they still have a formula in them.
When I run the code below it still goes to the end even if the cells are
blank.


Dim LastCell as Range
set LastCell = Range("A1").End(xlDown)
'assuminmg there are no embedded blank cells
set LastCell = Range("A" & Rows.Count).End(xlUp)
cell.select


TIA BigH

 

[Dave Peterson]

Cells with formulas aren't empty.

One way around it is to use something like:

Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet
LastRow =
..Evaluate("=LOOKUP(2,1/($a$1:$a$65535<>""""),ROW($a$1:$a$65535))")
LastCol = .Evaluate("=LOOKUP(1,1/($1:$1<>""""),COLUMN($1:$1))")

'then use those two variables where you want them.
.cells(lastrow,lastcol).value =....
end with

I use column A and row 1 in my code. Change it/them to the column/row you want.

 

[BigH]

David,

I cannot get your code to work, as I am new to programming i don't
understand how to use 'then use those two variables where you want them.

.cells(lastrow,lastcol).value =....

within the code.
The code I have attached below almost does what I want however it copies
the blanks as well. Maybe you could help me incorporate your code with what
I currently have.

Sub AlmostThere()
Dim Lrow As Long
Dim Drng As Range
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
Set Drng = Range("B3:H" & Lrow)
Drng.Copy
Range("Q1500").End(xlUp).Offset(1, 0).PasteSpecial
xlPasteValues
Lrow = Cells(Rows.Count, "J").End(xlUp).Row
Set Drng = Range("J3:O" & Lrow)
Drng.Copy
Range("Q1500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("A1").Select

End Sub

regards Harry

 

[BigH]

David,

I cannot get your code to work, as I am new to programming i don't
understand how to use 'then use those two variables where you want them.

.cells(lastrow,lastcol).value =....

within the code.
The code I have attached below almost does what I want however it copies
the blanks as well. Maybe you could help me incorporate your code with what
I currently have.

Sub AlmostThere()
Dim Lrow As Long
Dim Drng As Range
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
Set Drng = Range("B3:H" & Lrow)
Drng.Copy
Range("Q1500").End(xlUp).Offset(1, 0).PasteSpecial
xlPasteValues
Lrow = Cells(Rows.Count, "J").End(xlUp).Row
Set Drng = Range("J3:O" & Lrow)
Drng.Copy
Range("Q1500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("A1").Select

End Sub

regards Harry

 

[Dave Peterson]

Lrow is only used to determine what to copy--not to determine where to paste?

Option Explicit
Sub AlmostThere()
Dim Lrow As Long
Dim Drng As Range

LRow = activesheet.Evaluate _
("=LOOKUP(2,1/($b$1:$b$65535<>""""),ROW($b$1:$b$65535))")

Set Drng = Range("b3:h" & Lrow)

Drng.Copy
Range("Q1500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'--------

LRow = activesheet.Evaluate _
("=LOOKUP(2,1/($j$1:$j$65535<>""""),ROW($j$1:$j$65535))")

Set Drng = Range("J3:O" & Lrow)

Drng.Copy
Range("Q1500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

 

[BigH]

Hi Dave,



The code below still picks up the cells with formulas in them and pastes
them as zero's, is it possible to just copy the cells with values in them??



Regards Harry

 

[Dave Peterson]

What's the formula?

And if you use this array formula in a cell, what do you get:
=LOOKUP(2,1/($b$1:$b$65535<>""),ROW($b$1:$b$65535))
(ctrl-shift-enter)

I've never seen this fail.

Remember that if the formula returns a 0 (or " "), then it's not "blank".

Hi Dave,

The code below still picks up the cells with formulas in them and pastes
them as zero's, is it possible to just copy the cells with values in them??

Regards Harry