VBA : get number of rows in a spreadsheet

Is there any way in VBA that I can find the number of rows used on a
spreadsheet (the active worksheet)?  The reason I need this is because
I am trying to add some extra lines to a sheet (and move the following
rows down the sheet).

Cheers
Bill Dallas
0
8/12/2004 3:33:03 PM
excel 39879 articles. 2 followers. Follow

1 Replies
745 Views

Similar Articles

[PageSpeed] 28

Bill,

Dim myRow As Long

myRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row

The above will often report more rows than those actually used, so often it
is better to check a specific column for the last entry:

myRow = Range("A65536").End(xlUp).Row

HTH,
Bernie
MS Excel MVP

"Bill Dallas" <bill.dallas@gb.abb.com> wrote in message
news:20d56b6b.0408120733.3a40bd2a@posting.google.com...
> Is there any way in VBA that I can find the number of rows used on a
> spreadsheet (the active worksheet)?  The reason I need this is because
> I am trying to add some extra lines to a sheet (and move the following
> rows down the sheet).
>
> Cheers
> Bill Dallas


0
Bernie
8/12/2004 3:46:08 PM
Reply:

Similar Artilces:

I need to add mew rows for different spots.
I have this data: 06/01/02 ABC 06/02/02 BAC 06/03/02 CAD 07/01/02 NAD 07/02/02 CAD 07/03/02 BAD I need to add new rows after 06/03/02 and 07/03/02 everyday. Can you help me to automate the process? There are formulas for second colums and I need those formulas to be included at new rows. Thank you in advance ...

queries written inside MS-Access are getting deleted
Some of the queries I have written inside MS-Access are getting deleted automatically. And while I run the queries through code, I get this error 'Query should have one destination field' Explanation: I created a query in MS-access. Ran it from the code. Closed the database. Started it again, and now for that particular query, it is showing 'SELECT ;' only. Strange. I am in panic mode now What can be the possible reason? My first thought is that the database file is corrupt. Unfortunately it's a type of corruption that Compact and Repair probably won...

Get Access Data into Excel
Hi All, I am using excel macro to get data from access database. My sql query gives me 5 records or more than that. I am able to pull it different cells. But I want all the 5 results in single cell. Please help im not exactly sure what your trying to do, but maybe something like this will do the trick dim accval as string accval=rs!:XXX rs.movenext accval=accval & " " & rs!:xxx loop it till rs.eof=true hth dmoney "fi.or.jp.de" wrote: > Hi All, > > I am using excel macro to get data from access database. >...

Unhide rows?
In Excel 2002, rows that were previously hidden will not "unhide." Rows that are hidden now will only unhide by using the undo command. Columns hide and unhide successfully, but rows do not. Protection is not on for either worksheet or workbook. Does anyone have an idea why this is happening and how to fix it? Nevermind. I forgot that I had a column with instructions to filter certain rows, the rows I couldn't unhide -- gee, what a coincidence. "Scot" <greatscotinla@hotmail.com> wrote in message news:9Ea4f.1079$fc7.1027@newsread3.news.pas.earthli...

Outlook total number of emails bottom of blue pane how?
I used to have a total count of emails on the bottom of the blue pane. It's gone! E.g. Each folder I used to click on displayed the number of emails on the bottom of the blue navigation pane. All the help boxes say right click on folder - properties and display number but that gives it after each folder name. I don't want that Its ok - View and status bar fixed it - thanks "cstanley" wrote: > I used to have a total count of emails on the bottom of the blue pane. It's > gone! > E.g. Each folder I used to click on displayed the number of emai...

BP Inventory Item number
The field length of the item number in business portal is shorter than what is in Microsoft Dynamics GP. Any sales inventory item that is entered into great plains and is longer than the field requirements of Business Portal, you will have problems with. This may be why the system does weird stuff, like not filling in or saving correctly. Make sure you change it in the purchase order, since it will come across as a non-sales inventory item. This is a problem with Requisition Manager. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions wi...

Help!!Getting audio files to play continuously during slide show?!
I have Powerpoint 2002, and i am having a hard time getting the music file to play continuously during the slide show. it plays on the first slide (which is the only slide that has the little speaker picture on it), but then it doesn't transition to the next slide until after the song is done, when i want the song to play while the slides are transitioning. HELP please! Mega, Have a look here and see if it helps, there are tuturials in video demonstrating the process. http://www.pptfaq.com/FAQ00047.htm -- Luc Sanders MVP - PowerPoint "MegaB86" <MegaB86...

Counting number of rows based on mutiple criteria
Does anyone know how to count the number of rows based on mutiple criteria on other columns? For example I want to count the number row that meet the criteria of "Yes" in Col 2 and "Yes" in Col 3. The result would be 2 in the example below. I tried different combinations of Vlookup, Countif and Sumif and could not come up with anything that worked. Col 1 Col2 Col3 A Yes No B Yes Yes C No No D No Yes E Yes Yes Your help is greatly appreciated! Hi, =SUMPRODUCT((B1:B10=&qu...

Excel 2002 is scrolling to the very bottom of my spreadsheet
When I open Excel 2002 my spreadsheets are scrolling to the bottom by itself. If I move it back to the begining it scrolls back to the end. What is going on? Perhaps Speech Recognition is turned on. It can cause some strange behaviour. Choose Tools>Speech>Speech Recognition (if it had a check mark, it was turned on) Dennis wrote: > When I open Excel 2002 my spreadsheets are scrolling to the bottom by itself. > If I move it back to the begining it scrolls back to the end. What is going > on? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contexture...

getting addresses from 2000 to 2002
I am having trouble getting email addresses I added in outlook 2000 on a 98 box to outlook 2002 on an XP box. We are on an exchange server and for some reason when I added addresses to my address book they did not get saved on the exchange server. Now I can't see them on my XP box. Any ideas how I can get those addresses from my 98 box to my XP box?......thanks....Dave Where were the addresses being stored were they in the Mailbox Contact folder or were they in a PAB (Personal Address Book). "Dave" <dfinkenbinder@lextron-inc.com> wrote in message news:1bfe401c4217...

SQL Table SOP40500 Sales Master Number Setup
Can some rows be deleted from Sql Table SOP40500 to improve performance? Thank You, Brian ...

Custom number format with colors
Hi, I would like to format my numbers this way: > 0 --> Green = 0 --> Black < 0 --> Red How can I do that? Thanks!! Go to Format->Conditional Formatting... set your specs and click the format button to format the text the way you want. You will do a seperate one for each color. Select the entire sheet first though (CTL-A) -- -John Please rate when your question is answered to help us and others know what is helpful. "andre.labonte@gmail.com" wrote: > Hi, > > I would like to format my numbers this way: > > > 0 --> Green > = ...

random number #2
how can i create a normally distributed random number and a random number that has a beta distribution i need these both to be integer outputs thanks for the help Use NORMDIST and BETADIST -- Gary's Student "agw8@kent.ac.uk" wrote: > how can i create a normally distributed random number > and a random number that has a beta distribution > i need these both to be integer outputs > thanks for the help I think you meand NORMINV and BETAINV. See my reply under the OP's repeat of this question. It is generally better to reply to your original thread with fol...

how do i get font style back on toolbar?
new to this...when i started my document the font style was displayed on toolbar...now it is not...what do i do and thanks to those who help!!!!!!1 Melissa, try view, toolbars, and check formatting -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "melissa" <melissa@discussions.microsoft.com> wrote in message news:852099EC-03A0-4B0F-91AD-0B9F61CF8C4B@microsoft.com... > new to this...when i started my document the fon...

Get Mallika Sherawath Bathing Videos in all Angles
Get Mallika Sherawath Bathing Videos At http://blogcreationandhosting.blogspot.com/2009/11/1-per-click-in-adsense-programs.html < Due to some issues,I have hidden these videos inside an image.In that website , click on the big vertical Image on right side of website & get the videos, Enjoy it > ...

Restricting number of viewings?
Without using macros, is there a way to allow someone to view an application only once or twice, after which point they could no longer view the spreadsheet (for example, if I want to send a worksheet sample that I don't want to be used permenantly). I don't think so, you can restrict access with macros but this can be overcome by opening the workbook with macros disabled. You can also write code to prevent a workbook being opened unless macros are enabled, but this is overcome quite easily by anyone with a little knowledge of Excel and VBA. In short, Excel is not designed for securit...

Page numbering 12-04-09
I have taken the trouble to review all the related previous posts but do not see the answer to meet my needs. I have a 7 page document which consists of a front and back cover 2 pages each and so far 3 pages of report. Page 2 is followed by a section break next page while page 5 is followed by a section break next page. In addition I also put a section break at the end of the document which I should not need. Numbering starts on page 3 as page 1 of Y which seems to work fine. However, page 4 and 5 do not display page numbers while page 6 displays page 1 of 6 and page 7 i...

Editing a shared spreadsheet simultaneously
We are currently networked using Excel 2000. If one of us has a spreadsheet open, the other can only open the same spreadsheet in 'Read Only'. Is there a newer version of Excel which will allow two people to have open, and edit, and same worksheet at the same time? If not, anyone know of another program which will allow this? Hi Alfred you can "share" a workbook in excel 2000 (which allows for more than one person to open & edit it at once) ... have a look in Help you'll find details on how to do it, but basically the steps are: On the Tools menu, click Shar...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

spreadsheet does not print as large as created
I have created a spreadsheet in Excel 2002. Simple layout with number, name, address,blank, blank,number. I created with large font size (28 or 32). I want the columns large enough for me to write in blanks and make notes. When the document is printed (hp laser 5p) the size of the font is not increased. Jim Morgan Memphis Tn Probably because that printer (driver) will not support that font and/or size. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Jim Morgan" <jmorgan7@midsouth.rr.com> wrote in message news:06ce01c34dfe$29c53d90$a401280a@phx.gb...

Error with VBA code
I am having error with this statement. Do anyone know what's the error with this line: ..CheckBoxes(i).Value = False Dim i As Integer For i = 1 To 19 ..CheckBoxes(i).Value = False Next i I would guess that you have fewer than 19 checkboxes. -- Jim <xiaodan86@hotmail.com> wrote in message news:1178702221.638576.248520@q75g2000hsh.googlegroups.com... |I am having error with this statement. Do anyone know what's the error | with this line: | .CheckBoxes(i).Value = False | | | Dim i As Integer | For i = 1 To 19 | .CheckBoxes(i).Value = False | Next i | ...

get paid now!!!
when you subscribe you'll be rewarded 10$$$ Check it now,don't wait!!!!!!! http://e-mailpaysu.com/members/index.cgi?andrusko ...

Select non-consecutive rows
Hi, Is there a way to select non-consecutive rows besides Ctrl + click. If I want to insert a blank row above about 40 rows, this can become quite tedious. -- Thanks! Dee Hi only possible with VBA in this case -- Regards Frank Kabel Frankfurt, Germany "dee" <dee@discussions.microsoft.com> schrieb im Newsbeitrag news:23B3F46E-C956-4FCC-9880-08B9A200B3A2@microsoft.com... > Hi, > > Is there a way to select non-consecutive rows besides Ctrl + click. If I > want to insert a blank row above about 40 rows, this can become quite tedious. > > > -- > Tha...

Moving a row from one sheet to another wrongly leaves blank-row artifact.
Moving an entire row within a sheet in Excel XP is implemented correctly: Select the entire row, cut it, go to the target location, and Insert Copied Cells. The target row is inserted (pushing all rows below it down), and the source row is deleted (bringing all rows below it up, as it were). NOT so when the target is in another sheet in the same workbook. Excel inserts the target row correctly, but fails to delete the source row. The row still exists (only it's blanked out). You have to remeber to, tediously, return to the source and delete the blank row. Which means that if you intende...

How can I go to a specific page number in a large document
How do I go to a specific page number in a large word document. Read the replies to your earlier identical post -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "yisaajao" <yisaajao@discussions.microsoft.com> wrote in message news:CFC4289D-FC93-4C1B-951B-DB08CED833...