xlUp Help Please

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
I have the following code I am trying to use this to find the last value grater than zero. This code finds zero values how can I get it to ignor zero values

LastB = Range("B65536").End(xlUp).Row
LastOfAll = LastB
Range("I" & LastOfAll).Select
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi nehpets! How about using AutoFilter method?

With Columns("B:B")
.AutoFilter field:=1, Criteria1:="<>0"
Range("B65536").End(xlUp).Offset(0, 7).Select
.AutoFilter
End With
 
Upvote 0
I am trying to do this on selected columns only to set up a print area This is the code I am using at the moment but it dosnt ignor zero values



Range("B65536").Offset(0, a).Select
lastb = Range("B65536").End(xlUp).Row
LastC = Range("C65536").End(xlUp).Row
LastG = Range("G65536").End(xlUp).Row
LastH = Range("H65536").End(xlUp).Row
LastI = Range("I65536").End(xlUp).Row


LastOfAll = lastb

If LastC > LastOfAll Then
LastOfAll = LastC
End If
If LastG > LastOfAll Then
LastOfAll = LastG
End If
If LastH > LastOfAll Then
LastOfAll = LastH
End If
If LastI > LastOfAll Then
LastOfAll = LastI
End If

Range("I" & LastOfAll).Select
'
End Sub
 
Upvote 0
Hello again, Pls try this.<pre>
Sub test()
Dim arr, arrERow, i As Integer
Application.ScreenUpdating = False
arr = Array("B", "C", "G", "H", "I")
ReDim arrERow(LBound(arr) To UBound(arr))
For i = LBound(arr) To UBound(arr)
With Columns(arr(i))
.AutoFilter field:=1, Criteria1:="<>0"
arrERow(i) = Cells(65536, arr(i)).End(xlUp).Row
.AutoFilter
End With
Next
Application.ScreenUpdating = True
Range("I" & Application.WorksheetFunction.Max(arrERow)).Select
End Sub</pre>

regards,
Colo
This message was edited by Colo on 2002-04-16 02:53
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top