Ignoring Blank Cells in a range
 

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

Ignoring Blank Cells in a range
 

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 wrote:

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

Ignoring Blank Cells in a range
 

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" wrote in message
...
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 wrote:

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

Ignoring Blank Cells in a range
 

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" wrote in message
...
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 wrote:

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

Ignoring Blank Cells in a range
 

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 wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Ignoring Blank Cells in a range
 

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Ignoring Blank Cells in a range
 

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".

BigH wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson