Counting Selected/Highlighted Rows

Need to modify this maco to not count hidden rows.  For example if seven rows 
of ten rows in question are hidden, I need the macro to count only the three 
visible on screen.

Also, if no rows were hidden, and all ten were selected (highlighted), the 
macro should count ten.

In other words, it should count only what is selected/visible on screen.

Sub CountHighlightedRows()
MsgBox "Rows Selected: " & Selection.Rows.Count
End Sub
0
Utf
1/28/2010 7:51:13 PM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
866 Views

Similar Articles

[PageSpeed] 51

Not sure if I understand, but try this:

Sub fj()
   Dim x As Long
    x = Selection.SpecialCells(xlCellTypeVisible).Count
    MsgBox x
End Sub


"Phil H" <PhilH@discussions.microsoft.com> wrote in message 
news:CE24AAB5-B94A-4ADE-9021-10C337D849FC@microsoft.com...
> Need to modify this maco to not count hidden rows.  For example if seven 
> rows
> of ten rows in question are hidden, I need the macro to count only the 
> three
> visible on screen.
>
> Also, if no rows were hidden, and all ten were selected (highlighted), the
> macro should count ten.
>
> In other words, it should count only what is selected/visible on screen.
>
> Sub CountHighlightedRows()
> MsgBox "Rows Selected: " & Selection.Rows.Count
> End Sub 


0
JLGWhiz
1/28/2010 7:58:29 PM
I noticed an anomaly about using the xlVixible property to count the rows. 
If you use the Rows.Count, it only counts to down to the first hidden row. 
But if you only select one column and count the special cells visible 
property, it give the correct count of rows not hidden.


"Phil H" <PhilH@discussions.microsoft.com> wrote in message 
news:CE24AAB5-B94A-4ADE-9021-10C337D849FC@microsoft.com...
> Need to modify this maco to not count hidden rows.  For example if seven 
> rows
> of ten rows in question are hidden, I need the macro to count only the 
> three
> visible on screen.
>
> Also, if no rows were hidden, and all ten were selected (highlighted), the
> macro should count ten.
>
> In other words, it should count only what is selected/visible on screen.
>
> Sub CountHighlightedRows()
> MsgBox "Rows Selected: " & Selection.Rows.Count
> End Sub 


0
JLGWhiz
1/28/2010 8:07:47 PM
This is sort of a gerry rig but it works and will do what you want.

Sub fl()
    Dim x As Long
    x = Selection.SpecialCells(xlCellTypeVisible) _
    .Count / Selection.Columns.Count
    MsgBox x
End Sub




"JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message 
news:OCC8AXFoKHA.1544@TK2MSFTNGP02.phx.gbl...
>I noticed an anomaly about using the xlVixible property to count the rows. 
>If you use the Rows.Count, it only counts to down to the first hidden row. 
>But if you only select one column and count the special cells visible 
>property, it give the correct count of rows not hidden.
>
>
> "Phil H" <PhilH@discussions.microsoft.com> wrote in message 
> news:CE24AAB5-B94A-4ADE-9021-10C337D849FC@microsoft.com...
>> Need to modify this maco to not count hidden rows.  For example if seven 
>> rows
>> of ten rows in question are hidden, I need the macro to count only the 
>> three
>> visible on screen.
>>
>> Also, if no rows were hidden, and all ten were selected (highlighted), 
>> the
>> macro should count ten.
>>
>> In other words, it should count only what is selected/visible on screen.
>>
>> Sub CountHighlightedRows()
>> MsgBox "Rows Selected: " & Selection.Rows.Count
>> End Sub
>
> 


0
JLGWhiz
1/28/2010 8:13:39 PM
This will count all the visible rows in your selection.  Hope this helps!  If 
so, let me know, click "YES" below.

Option Explicit

Sub CountHighlightedRows()

Dim rw As Range
Dim VisibleRows As Long

    For Each rw In Selection.Rows
        If rw.Hidden = False Then
            VisibleRows = VisibleRows + 1
        End If
    Next rw

    MsgBox "Rows Selected: " & VisibleRows, vbInformation
    
End Sub
-- 
Cheers,
Ryan


"Phil H" wrote:

> Need to modify this maco to not count hidden rows.  For example if seven rows 
> of ten rows in question are hidden, I need the macro to count only the three 
> visible on screen.
> 
> Also, if no rows were hidden, and all ten were selected (highlighted), the 
> macro should count ten.
> 
> In other words, it should count only what is selected/visible on screen.
> 
> Sub CountHighlightedRows()
> MsgBox "Rows Selected: " & Selection.Rows.Count
> End Sub
0
Utf
1/28/2010 8:14:02 PM
Hi,

One way

Sub CountHighlightedRows()
For Each c In Selection.Rows
If c.RowHeight > 0 Then
    viz = viz + 1
End If
Next
MsgBox viz
End Sub
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Phil H" wrote:

> Need to modify this maco to not count hidden rows.  For example if seven rows 
> of ten rows in question are hidden, I need the macro to count only the three 
> visible on screen.
> 
> Also, if no rows were hidden, and all ten were selected (highlighted), the 
> macro should count ten.
> 
> In other words, it should count only what is selected/visible on screen.
> 
> Sub CountHighlightedRows()
> MsgBox "Rows Selected: " & Selection.Rows.Count
> End Sub
0
Utf
1/28/2010 8:18:01 PM
Thanks for the help!

"Ryan H" wrote:

> This will count all the visible rows in your selection.  Hope this helps!  If 
> so, let me know, click "YES" below.
> 
> Option Explicit
> 
> Sub CountHighlightedRows()
> 
> Dim rw As Range
> Dim VisibleRows As Long
> 
>     For Each rw In Selection.Rows
>         If rw.Hidden = False Then
>             VisibleRows = VisibleRows + 1
>         End If
>     Next rw
> 
>     MsgBox "Rows Selected: " & VisibleRows, vbInformation
>     
> End Sub
> -- 
> Cheers,
> Ryan
> 
> 
> "Phil H" wrote:
> 
> > Need to modify this maco to not count hidden rows.  For example if seven rows 
> > of ten rows in question are hidden, I need the macro to count only the three 
> > visible on screen.
> > 
> > Also, if no rows were hidden, and all ten were selected (highlighted), the 
> > macro should count ten.
> > 
> > In other words, it should count only what is selected/visible on screen.
> > 
> > Sub CountHighlightedRows()
> > MsgBox "Rows Selected: " & Selection.Rows.Count
> > End Sub
0
Utf
1/28/2010 8:30:07 PM
Thanks for the help!

"Mike H" wrote:

> Hi,
> 
> One way
> 
> Sub CountHighlightedRows()
> For Each c In Selection.Rows
> If c.RowHeight > 0 Then
>     viz = viz + 1
> End If
> Next
> MsgBox viz
> End Sub
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Phil H" wrote:
> 
> > Need to modify this maco to not count hidden rows.  For example if seven rows 
> > of ten rows in question are hidden, I need the macro to count only the three 
> > visible on screen.
> > 
> > Also, if no rows were hidden, and all ten were selected (highlighted), the 
> > macro should count ten.
> > 
> > In other words, it should count only what is selected/visible on screen.
> > 
> > Sub CountHighlightedRows()
> > MsgBox "Rows Selected: " & Selection.Rows.Count
> > End Sub
0
Utf
1/28/2010 8:31:01 PM
Reply:

Similar Artilces:

Adding new rows
I have a spreadsheet with 100 rows in, I need to add a row after each current row, is there a quick way to do this or have i got to insert each new row individually? -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=390760 I assume you want to add an empty new row in between the existing rows. One way would be with an extra column Save as "trial" first.......just in case thi...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

Delete 3 end row
Hi I want to delete three end row of any sheet. any sheet has different row number. Thank's for any help. your's Hi, This will clear the last 3 rows of the active sheet. Note I the on error line is to cope when there are less than 3 rows on the sheet Sub del_Last_3() Dim LastRow As Long On Error Resume Next LastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row ActiveSheet.Rows(LastRow - 2 & ":" & LastRow).ClearContents End Sub -- Mike Whe...

Prinding each row of Excel sheet on a seperate page dispallying one record on each pa
From a spread sheet ,I need to print out a row on each page seperately that is printout each record on the row on one page seperately. Can any one please advise how it can be done ? Fro example Row 1 Data 11 Data 12 Data 13 Row 2 Data 21 Data 22 Data 23 Row 3 Data 31 Data 32 Data 33 I would want to print out Data 11 and Data 22 on one page then Data 21 and Data 22 on the second page and data 31 and Data 33 on the third page Pleae advise if and hwo this is possible. Thank you Sanjeev --- Message posted from http://www.ExcelForum.com/ Sorry ...

Selecting Text Box
Is there a keyboard command that one can use with the mouse to select the text box (frame) rather than trigger the Edit Text mode? When there are many objects on the page, getting the mouse to click on the right frame can be a real problem. But if the cursor is in the right box, some keyboard command (with or without the mouse) should help select the right object. Thanks! ...

counting using multiple criteria
Hi, I have a problem for which I can't seem to find a working solution. On the one hand I have a vertical table with employee names, the number of years they have been with the company, and all this sorted by the employee's age (in years). This table will be updated from time to time, and as such it doesn't have a fixed length. On the other hand I have a table which shows the number of employees in a particular age category (-20, 21-25, 26-30, etc) horizontally, and the number of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, etc) vertically. ...

SQL select statement question
My dataset has 3 columns: customer id, store location, charges I'd like to write a single select statment that will show each customer id once, with the store location where they have the most charges, and the sum of the charges for that store location. This gives the max charges for each customer, but doesn't give the location: select a.custID, max(new.amt) from smallchg a inner join; (select custID, location, sum(charges) as amt from smallchg group by custID, location) as new ; on a.custID= new.CustID group by a.CustID thanks. joel Try this -- SELECT TOP 1 ...

lookup row number
Hi, can you tell me how to lookup the row number of a name in a list. EG say I have a list of names as below: Andrew Mike Steve Ian Paul I would normally just add a column with the row numbers and then lookup the row number in the second column but is it possible to do without adding the extra column by using the row formula someohow? Thanks Hi Michael Try the below.. =MATCH("Mike",A:A,0) -- Jacob (MVP - Excel) "Michael" wrote: > Hi, > can you tell me how to lookup the row number of a name in a list. > > EG say I hav...

count in cell
Does anybody know how I can count the number of characters in a cell, I know how to use the 'count' and 'counta' function to count the number of cells used but not in a cell. Thanks =LEN(A1) -- ---------------------------- Mauro Gamberini "Joe" <someone@microsoft.com> ha scritto nel messaggio news:%23sMRof72EHA.1404@TK2MSFTNGP11.phx.gbl... > Does anybody know how I can count the number of characters in a cell, I > know > > how to use the 'count' and 'counta' function to count the number of cells > > used but not in a c...

Can I wrap rows of print?
I have twelve columns I must print out in a report and don't want to print on two pages. Can I "wrap" the columns from the second page when printing so they print beneath the corresponding information from the first page? For example: Name Address City State Zip Code Cost Project Date Name Address City State Zip Code Cost Project Date Storm, Not directly. Excel prints sheets pretty much as-is, with the acoutrements available in File - Page Setup. What you could do is make a mirror sheet in the layout you want, using links to the original. Then print that...

Auto copy and insert a defined number of rows as defined in a cell
Hi I don't know much about VBA but could probably work something out. I need to produce carton labels from a spreadsheet using Word to mail merge and the labels need to include 1 of 10, 2 of 10 on them. The number of labels required is dependant on a number which is included for each row of data in the spreadsheet. What I am doing at the moment is copying each row and insert pasting the additional number of rows required. In the new rows I then add 1 of 10 in the first row, 2 of 10 in the second row etc. etc. This then enables me to perform the mailmerge. I have around 300...

Hide Rows
Can anyone help me with the following problem? I have a drop down list, e.g. with 4 options. each option is represented by a row of data, and each cell in each row is to be entered by the user. (That means I have 4 rows of data.) How do I unhide a certain row that is selected via the drop down list whilst hiding the rest of the 3 rows? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial ...

code to delete rows
this is probably very simple but i am new to writing code (excel 2000). i want to scan cells M9:M999, and for every cell where the value is 0 i want that row deleted. so for instance M9's value is 453 and row 9 will stay, but M10's value is 0 so row 10 is deleted. so on and so forth. thanks so much! ianalexh Do a filter (Data>Filter>Autofilter) and select a value of 0. Delete the visible rows. -- HTH RP (remove nothere from the email address if mailing direct) "ianalexh" <ianalexh@discussions.microsoft.com> wrote in message news:312D8E40-EBD6-426C-A98...

How to mail merge multiple rows in one document 03-11-10
Hi, I have a worksheet that lists subscribers of a service. It looks something like this: Organization Name Title Email ACME CO. Smith, Sam CEO ssmith@acme.com ACME CO. Jones, Bill Staff bjones@acme.com Each organization has one president and multiple staff members listed (between 1 and 5 staff members). I want to email each president, through mail merge, and give them a list of all the current subscribers from their organization (data from other rows in the worksheet). Is this possible to do (using Office 2...

Row, Column measurement?
Excel 2000. What are the units? Why aren't they the same? If the row is 20 tall & the column 20 wide, why don't I have a square cell? How do I get one? -- Thanks in advance... Bob Try Format,raw height 11.25 Format,column width 1.43 "Bob Newman" wrote: > Excel 2000. > What are the units? Why aren't they the same? If the row is 20 tall & the > column 20 wide, why don't I have a square cell? How do I get one? > > -- > Thanks in advance... Bob > > > Nope. Bob "Sakai" <Sakai@discussions.microsoft.com...

Workbook automatically unhiding rows upon opening
We have a shared workbook that our admin staff updates, and our sales staff accesses when on the road. Recently, some of the admin's computers, and all of the sales staff's laptops have begun unhiding all of the rows in this workbook when they open it. For others, the hidden rows stay hidden. I've been tasked with making sure the rows stay hidden upon opening, but I cannot figure out why this is happening. Help? It could be the two groups are actually accessing two different similarly named files.....could be same name in two different directories....problem is common in...

In Excel I have a workbook, and want to select all blank rows for.
I have a worksheet with around 19,000 rows, some of which are blank. It's a price list I do regularly. How can I select all blank rows and then delete them. It will take hours to do them all manually. Hi see: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows -- Regards Frank Kabel Frankfurt, Germany "HANDY ANDY" <HANDY ANDY@discussions.microsoft.com> schrieb im Newsbeitrag news:FC815E8C-813A-48EA-94A3-26E8B22F8EB4@microsoft.com... > I have a worksheet with around 19,000 rows, some of which are blank. It's a > price list I do regularly. How can ...

fill a color to a row
Hi, I want to check a column (lets say H) if there is a 1 value i want excel to color the row. lets say there is a 1 on H6, I want to fill a color to row 6. It can be full coloring or if i can i might like to range it to A6:J6 thanx in advance formula = =$h1=1 -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "mns" <mns@discussions.microsoft.com> wrote in message news:E1BB615E-FA14-4DDA-99AE-3CD7BD28D5FD@microsoft.com... > Hi, > I want to check a column (lets say H) if there is a 1 value i want excel > to > c...

MACRO for moving rows from one worksheet to another
I have a workbook that has different classifications based on a dates (Classifications = Red, Green, Yellow). On the cumulative sheet I have all and then I have a separate sheet for each class. Using macros I want to copy the rows (B-M) that contain the class to its corresponding sheet (keeping the cumulative sheet populated). I have a formula to notate which row contains the appropriate color (Column A). Each sheet has the same header as the cumulative sheet. The most effective way is to create your own macro. Turn on the macro recorder, go through the steps to copy your d...

Selected Criteria or All
Hi. I have a form that runs reports depending on criteria selected by the user. The form contains a Combo Box that contains 'Types'. I have populated the combo box with the different available 'types' and an 'All Types' option. In the query, I have placed the following in the Criteria for the SAR_Type field. IIf([forms]![frmSAR_Statistics]![cmbSAR_Type]<>"All Types",[forms]![frmSAR_Statistics]![cmbSAR_Type],"*") What I am trying to achieve is for the query to show either the Selected 'Type' or All Types. So the IIf statement...

Counting the number of different entries in a column
Right! column A contains area codes (two digit numbers, eg. 30), column B customer numbers (eg 999999) The lists then go down listing orders. I want to count how many different customers have placed an order per area (not counting duplicates) Thanks for any help With the query area code in cell C1 enter the below formula. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both e...

Box highlighting highest score in range
I have a series of tables which are ranked by row. Whilst the rank defines who was the best in any given category, I would like to automate a box around the 'best' number to visually impact my audience. Does anyone know how I can achieve this? Many thanks in advance Keith C Take a look at conditional formatting in XL Help. In article <dea4136b.0311020710.51f73cf9@posting.google.com>, shaddyho@hotmail.com (shaddy) wrote: > I have a series of tables which are ranked by row. Whilst the rank > defines who was the best in any given category, I would like to > automate...

how do i have my formula wrap to the next row in excel
Hello to anyone that can help me. I'm trying to use excel to create a payment chart for my bills, so i would like to know how to have this formula wrap to the next row with the ending total. A+B-C-D-E=F and have the F wrap as the total wrap back to A and continue on through the cells Please help. Thank you for your time. Bobby That is not a formula to start with and you cannot wrap to the next row in any case. Say you have numbers in A1,B1,C1,D1,E1 In F1 enter =A1+B1-C1-D1-E1 If you posted a small sample of your data and layout, we could be more specific. Gord Dibben MS Exce...

Open form for record selected in subform
Hello, I have 3 forms. The starting form (#1) has an unbound combobox from which users can select a clinic, and a subform (#2) in datasheet format. The subform runs off a query that is tied to the clinic that the user selects on the parent form. Because the subform is based on a query that ties several normalized tables together, I cannot edit the fields in that subform. Therefore, when the data display, I want to be able to click on the ID field of any record and have another form (#3) popup to show detailed, editable information for that specific record. I have tried setting ...

Macro for Deleting rows with balnk cell
Hi, i have several data in a sheet with data in 10 columns... in column A there are cells where it is blank and i want to delete the rows where there is blank cell in column A.. I need the vba macro for this regards, radha Try Sub DeleteBlankRows_2() 'This macro delete all rows with a blank cell in column A 'If there are no blanks or there are too many areas you see a MsgBox Dim CCount As Long On Error Resume Next With Columns("A") ' You can also use a range like this Range("A1:A8000") CCount = .SpecialCells(xlCell...