Try This.. Sub GetNonBlank() For i = 1 To ActiveWorkbook.Sheets.Count t = t + WorksheetFunction.CountA(Sheets(i).UsedRange) Next MsgBox t End Sub
On Thu, Feb 10, 2011 at 6:11 AM, Prathima R <prathima....@gmail.com> wrote: > please advice in this concern as i am having around 20-30 workbooks where > in need to count the number of Non-blanks in multilple sheets. > > Thanks, > Prathima > > On Thu, Feb 10, 2011 at 7:23 PM, Prathima R <prathima....@gmail.com>wrote: > >> >> Thanks for the response. >> >> Here am trying to count the number of non-blanks in multiple sheets >> >> >> >> On Thu, Feb 10, 2011 at 6:32 PM, Paul Schreiner >> <schreiner_p...@att.net>wrote: >> >>> Are you wanting to count the sheets >>> or count the records/rows on all sheets? >>> >>> Sheets.Count >>> >>> gets you the number of sheets. >>> >>> For Sht = 1 to sheets.count >>> total = total >>> + application.worksheetfunction.counta(sheets(sht).range("A1:A65000")) >>> next Sht >>> >>> would loop through all sheets and count the number of non-blank cells in >>> column "A". >>> >>> ?? >>> >>> Paul >>> >>> ------------------------------ >>> *From:* Prathima R <prathima....@gmail.com> >>> *To:* excel-macros@googlegroups.com >>> *Cc:* ashish koul <koul.ash...@gmail.com> >>> *Sent:* Thu, February 10, 2011 6:21:43 AM >>> *Subject:* $$Excel-Macros$$ Need Help >>> >>> >>> >>> Hi, >>> >>> i have multiple sheets and i want to count the number of instruments in >>> one go, with COUNTA am not able to get. I tried grouping the sheets but >>> could't get the count of all sheets. >>> >>> need your suggestion >>> Thanks, >>> Prathima >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> >> > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- Regards Rajan verma +91 9158998701 -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel