p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

vba_excel thread: How do you stop when you reach row *


Message #1 by "mike kerr" <mikezcg@y...> on Mon, 28 Jan 2002 18:03:06
This works as long as you don't have more than one "region" in the workbook.
If you have a region below the first region it will give you the last row in
the first region.

>-----Original Message-----
>From: ROMO,RICHARD (HP-USA,ex1) [mailto:richard_romo@h...]
>Sent: Tuesday, January 29, 2002 11:46 PM
>To: VBA_Excel
>Subject: [vba_excel] Re: How do you stop when you reach row *
>
>
>Hey who ever,
>
>A small caveat to Peters work.  If you need to find the end of information
>in a row: use currentRegion command.  Starting at column 1 and row 1 (A1)
>you can find where the information ends for columns or rows on your
>spreadsheet.  You can do this by using the code below:
>
>		Number = Wks.[A1].CurrentRegion.Rows.Count
>
>This will provide you with the number of rows that are filled with data.
>The above code goes to the end of data where it begins to find white spaces
>and calculates for you the number of rows that are filled.  This
>command can
>be used with columns for the same results:
>
>		Number = Wks.[A1].CurrentRegion.Columns.Count
>
>This will give you the upper bounds to an Array.  You can write a for loop
>and fill an array with contents of that row in a the spreadsheet
>and process
>that array as you see fit.  Slipping variable in, and out of the array.
>Processing them then write the contents back to the spread sheet.
>
>Wks is the name of a worksheet that you are working on.
>
>Sincerely,
>Richard Romo
>Phone:714-940-7227
>Cell Phone:626-695-0791
>
>
>
>-----Original Message-----
>From: Peter O'Connor [mailto:peter.oconnor@4...]
>Sent: Tuesday, January 29, 2002 12:38 AM
>To: VBA_Excel
>Subject: [vba_excel] Re: How do you stop when you reach row *
>
>
>To find last cell in column use the following code:
>'example using column A,
>Dim Bottom_Row as Single
>Dim Count as Single
>
>Range("a65536").Select
>Selection.End(xlup).Select
>Bottom_Row = ActiveCell.Row
>'do something with each cell in turn....
>For Count = 1 to Bottom_Row step 1
>'Do something
>ActiveCell.offset(1,0).select
>Next Count
>'or work on whole selection at once
>Worksheets("Sheet1").Range(Cells(1,1),Cells(1,Bottom_Row)).Select
>With Selection
>'Do Something ie
>.Font.Size = 12
>.Font.Colour = rgb(255,1,1)
>.Interior.colorindex =6
>End With
>With Selection
>.Names.add "My_Range",Referto:=Selection
>End With
>
>
>Hope this helps
>
>
>
>-----Original Message-----
>From: Joe Federico [mailto:joefederico@y...]
>Sent: 28 January 2002 22:48
>To: VBA_Excel
>Subject: [vba_excel] Re: How do you stop when you reach row *
>
>
>Not sure what you want to do with your 1st request.
>
>To turn off the screen display, use this code in your procedure before it
>executes:
>
>Application.ScreenUpdating = False
>
>Remember to turn it back on at the end of your procedure as well. It's
>been indicated this is not totally necessary but it's a good habit to get
>into:
>
>Application.ScreenUpdating = True
>
>Hope this helps!
>************************************************************************
>DISCLAIMER:This email and any files transmitted with it are private and
>confidential and intended solely for the use of the individual or entity to
>whom they are addressed. If you are not the addressee, you are not
>authorised to copy or use the information or to place any reliance upon it,
>nor should you copy it or show it to anyone. If you have received
>this email
>in error please notify nwsupport@a...
>************************************************************************
>
>


  Return to Index