Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 18th, 2006, 10:56 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Find the rowcount before blank cell

I am working on, let's say sheet2 and have data on sheet8 starting at "G7". I never know how much data will occupy the column, so
I'd like to find the rowcount of the last value before a blank cell in column "G7".

How would the code be written. I have tried many things, all of which are not returning what I am looking for.

Any suggestions?

Reply With Quote
  #2 (permalink)  
Old May 18th, 2006, 11:29 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hmmm... What exactly are you trying to get as a return value. Are you trying to find what is in G6, or F7, or are you looking for something else?

Mike

Mike
EchoVue.com
Reply With Quote
  #3 (permalink)  
Old May 18th, 2006, 02:11 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The return value I want is, well, Let's say for example data starts at "G7" and ends at "G20" and G21 is blank. I want the return value that counts from G7 to G21 which would be "14".

Reply With Quote
  #4 (permalink)  
Old May 18th, 2006, 02:13 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Actually counting from G7 to G20 which is 14. because it counted up to the last value before the blank cell value which was G21.

Reply With Quote
  #5 (permalink)  
Old May 18th, 2006, 02:18 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

In G21 just put the following function...

=COUNTIF(G1:G20,"<>")

Is that what you are looking for? Or are you hoping to do it using VBA?

Mike




Mike
EchoVue.com
Reply With Quote
  #6 (permalink)  
Old May 18th, 2006, 02:19 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Or =COUNTIF(G1:G100, "<>") would give the same answer as well.

Mike
EchoVue.com
Reply With Quote
  #7 (permalink)  
Old May 22nd, 2006, 03:05 PM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
In VBA, you could do something like:

Range("g7").Select
RowsOfData = ActiveCell.CurrentRegion.Rows.Count

This only works if the colums to the left and right, and the rows above cell G7 are empty - other wise will count extra rows - so may not be exactly what you're after?
Could always write a do loop with activecell.offset, checking for blank cell - but may be a bit like overkill!!

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to refer a cell within a cell like =RC[ RC2 chakravarthi_os Excel VBA 1 September 24th, 2006 08:19 AM
Rowcount from SQL query delwork SQL Language 5 January 6th, 2006 03:08 PM
Lose cell Text when editing cell in VSFlexGrid 6 bobcratchet VB How-To 0 July 30th, 2004 09:32 AM
Assigning a @@ROWCOUNT to a variable ioates SQL Server DTS 2 January 9th, 2004 03:16 AM



All times are GMT -4. The time now is 07:58 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.