Find Last Data In Columns

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
I have 8 columns B to I in these I have either 1 or 0.5 I need a macro that that goes down to the last entry in any of these columns and selects the entry in I.

I only need to find this data in columns B C G H I

I am setting it up to create a print area A1 to the active cell I hope this is clear.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I think there are quite a few ways of doing this.
One way of getting the last value in Column I would be:

MyValue = Range("I65536").End(xlUp).Value

Alternatively you could mess about with the SpecialCells feature:

LastCol = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

Specialcells is the equivalent of presing Ctrl+End - it takes you to the last used cell on your sheet.

These help?

Rgds
AJ
 
Upvote 0
On 2002-04-15 10:07, nehpets12 wrote:
I have 8 columns B to I in these I have either 1 or 0.5 I need a macro that that goes down to the last entry in any of these columns and selects the entry in I.

I only need to find this data in columns B C G H I

I am setting it up to create a print area A1 to the active cell I hope this is clear.

If you you last cell with data in a column was in Column D32 and In Column I it's I10,

This will bring you to I32.

Range("B1").Select
ActiveCell.SpecialCells(xlLastCell).Select

But what if I32 is empty? Or are you only using this to set the print area?

Edit: AJ beat me to it by 2 minutes!
This message was edited by Cosmos75 on 2002-04-15 10:17
 
Upvote 0
Those codes will not work if there is data in columns past I.

nehpets12: Does your data stop at column I?
 
Upvote 0
in columns "d" "e" "f" I have data that goes to the end of the sheet I only need to find the last data in columns "b" "c" "g" "h" "i" and select the active cell in coumn I
 
Upvote 0
So you want to find the last row in which a column has data and go to column I regardless of I has any data or not.

Only column B to I have data?

Are you using this ONLY to set the print area?
 
Upvote 0
I am only trying to set the print area

but the data I want to print is in columns B C G H or I

I need it to go to the last entry in any of these columns and goto column I so can set the print area a1 to activecell
 
Upvote 0
Then this'll work to select that range.

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

WARNING: BUT!!! It won't work if you have any data, formula past coumn I.
This message was edited by Cosmos75 on 2002-04-15 10:58
 
Upvote 0
Still not quite sure I get it, but if the last entry in ANY of columns B C G H or I will get us to the row you want, then use

MyValue = Range("I65536").End(xlUp).Value

to find the number of the last row with a value in column I, then:

Range("I" & myValue).Select

to get to where you want in column I?

Or just
Range("I65536").End(xlUp).Select
This message was edited by AJ on 2002-04-15 11:02
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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