Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Counting Non-hidden Cells In A Row

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I need to be able to count the number of cells in a row that contain numbers, but exclude the hidden columns. Here is a crude example:

Row1: 5 7 4 6 8 10 Count=6
Now I hide two rows
Row1: 5 7 8 10 Count=4


Any help is appreciated but no macros please. I need this to be as user friendly as possible. I also know that SUBTOTAL works great with this when the data is in a column but it doesn't want to work when I use it on a row like the example.


Similar Excel Video Tutorials

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Delete Hidden Worksheets
- This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop up for
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.

Similar Topics







Hi All,

Anyone have any idea why this doesn't work and can anyone think of any efficient ways of counting the number of hidden rows without looping through the usedrange?

Code:

wks.Columns.Count - wks.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count


FYI... the problem is that when there is are hidden row(s) its returning rows.count - the last row before the first hidden row.


I (a code witing novice) am trying to programatically add the values from a user form as a new row (record) in the Worksheet CSData. Each time the user form is completed and the cmd button "Save Entry" is clicked I need it to append the data into the next available row of the worksheet. I've searching through the forum for examples of how to do this and found what I thought might work. When I tried to use the attached code it broke on the first line after "With rng" .
Any help would be much apprciated!


Code:

Private Sub cmdSaveEntry_Click()
    Dim rng As Range
    Dim Row1 As Long
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("SCData")
    
    With ws1
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            With rng
                ws1.Cells(Row1, 1).Value = Me.cboCity.Value
                ws1.Cells(Row1, 2).Value = Me.txtFlightNo.Value
                ws1.Cells(Row1, 3).Value = Me.txtDate.Value
                ws1.Cells(Row1, 4).Value = Me.txtC1.Value
                ws1.Cells(Row1, 5).Value = Me.txtD1.Value
                ws1.Cells(Row1, 6).Value = Me.txtC2.Value
                ws1.Cells(Row1, 7).Value = Me.txtD2.Value
            End With
    End With
End Sub





Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these new
rows, I need to make my totals update to include all the new rows, but
the number of rows added is not static

This is what i have for trying to update the formulas when new rows are
added:
With Worksheets("Data")
..Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
..Cells((.UsedRange.Rows.Count - 1), 8) & ")"

..Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
..Cells((.UsedRange.Rows.Count - 1), 9) & ")"

..Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
..Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past




(See attached file for a clearer understanding)

I currently have two rows of data. I have used the SUMPRODUCT function to count the number of times that the value in the second row is less than the number directly above it in the first row.

There are 3 possible numbers in the first row: 3, 4, or 5.
I need to split these up so that I can have these three seperate categories.

When a value row1 = 3, I need to count the number of times that the number directly below it in row 2 is < 3.

When a value row1 = 4, I need to count the number of times that the number directly below it in row 2 is < 4.

Same deal with 5.

I have tried using an IF statement for when the value is 3:
=IF(B1:J1=3,SUMPRODUCT(--(B2:J2<B1:J1)))

But this doesn't work.

Any help is appreciated.


Is there a shorter code I can use to hide/unhide rows with a VBA button?

There are no "fixed" range number of rows between Header to Header; they are random. Example: There are less rows between Header 1 to Header 2 then there are rows between Header 2 to Header 3, etc.

Header 1 at (A2:K2)

range: ( A4 : K190 )

***hide every 6 rows*** (A4 :K9), (A11:K16),(A18:K23), etc.

***then hide 5 rows range*** (A186: K190 )

Header 2 at (A191:K191)

range: ( A193 : K386 )

***hide every 6 rows, range:*** ( A193 :K198), (A200:K205),(A207:K212), etc.

***then hide 5 rows range*** (A382: K386 )

Header 3 at (A387:K387)

range: ( A389 : K582 )

***hide every 6 rows*** ( A389 :K394), (A396:K401), (A403:K408), etc.

***then hide 5 rows range*** (A578: K582 )

Header 4 at (A583:K583)

range: ( A585 : K701 )

***hide every 6 rows*** ( A585 :K589), (A592:K597), (A599:K604), etc.

***then hide 5 rows range*** (A697: K701 )

Here is the code I used, but I had to type in every range for it to work correctly.

Code:

Sub Hidden()
Application.ScreenUpdating = False
Rows("4:9").Hidden = Not Rows("4:9").Hidden
Rows("11:16").Hidden = Not Rows("11:16").Hidden
Rows("18:23").Hidden = Not Rows("18:23").Hidden
Rows("25:30").Hidden = Not Rows("25:30").Hidden
Rows("32:37").Hidden = Not Rows("32:37").Hidden
Rows("39:44").Hidden = Not Rows("39:44").Hidden
Rows("46:51").Hidden = Not Rows("46:51").Hidden
Rows("53:58").Hidden = Not Rows("53:58").Hidden
Rows("60:65").Hidden = Not Rows("60:65").Hidden
Rows("67:72").Hidden = Not Rows("67:72").Hidden
Rows("74:79").Hidden = Not Rows("74:79").Hidden
Rows("81:86").Hidden = Not Rows("81:86").Hidden
Rows("88:93").Hidden = Not Rows("88:93").Hidden
Rows("95:100").Hidden = Not Rows("95:100").Hidden
Rows("102:107").Hidden = Not Rows("102:107").Hidden
Rows("109:114").Hidden = Not Rows("109:114").Hidden
Rows("116:121").Hidden = Not Rows("116:121").Hidden
Rows("123:128").Hidden = Not Rows("123:128").Hidden
Rows("130:135").Hidden = Not Rows("130:135").Hidden
Rows("137:142").Hidden = Not Rows("137:142").Hidden
Rows("144:149").Hidden = Not Rows("144:149").Hidden
Rows("151:156").Hidden = Not Rows("151:156").Hidden
Rows("158:163").Hidden = Not Rows("158:163").Hidden
Rows("165:170").Hidden = Not Rows("165:170").Hidden
Rows("172:177").Hidden = Not Rows("172:177").Hidden
Rows("179:184").Hidden = Not Rows("179:184").Hidden
Rows("186:190").Hidden = Not Rows("186:190").Hidden
Rows("193:198").Hidden = Not Rows("193:198").Hidden
Rows("200:205").Hidden = Not Rows("200:205").Hidden
Rows("207:212").Hidden = Not Rows("207:212").Hidden
Rows("214:219").Hidden = Not Rows("214:219").Hidden
Rows("221:226").Hidden = Not Rows("221:226").Hidden
Rows("228:233").Hidden = Not Rows("228:233").Hidden
Rows("235:240").Hidden = Not Rows("235:240").Hidden
Rows("242:247").Hidden = Not Rows("242:247").Hidden
Rows("249:254").Hidden = Not Rows("249:254").Hidden
Rows("256:261").Hidden = Not Rows("256:261").Hidden
Rows("263:268").Hidden = Not Rows("263:268").Hidden
Rows("270:275").Hidden = Not Rows("270:275").Hidden
Rows("277:282").Hidden = Not Rows("277:282").Hidden
Rows("284:289").Hidden = Not Rows("284:289").Hidden
Rows("291:296").Hidden = Not Rows("291:296").Hidden
Rows("298:303").Hidden = Not Rows("298:303").Hidden
Rows("305:310").Hidden = Not Rows("305:310").Hidden
Rows("312:317").Hidden = Not Rows("312:317").Hidden
Rows("319:324").Hidden = Not Rows("319:324").Hidden
Rows("326:331").Hidden = Not Rows("326:331").Hidden
Rows("333:338").Hidden = Not Rows("333:338").Hidden
Rows("340:345").Hidden = Not Rows("340:345").Hidden
Rows("347:352").Hidden = Not Rows("347:352").Hidden
Rows("354:359").Hidden = Not Rows("354:359").Hidden
Rows("361:366").Hidden = Not Rows("361:366").Hidden
Rows("368:373").Hidden = Not Rows("368:373").Hidden
Rows("375:380").Hidden = Not Rows("375:380").Hidden
Rows("382:386").Hidden = Not Rows("382:386").Hidden
Rows("389:394").Hidden = Not Rows("389:394").Hidden
Rows("396:401").Hidden = Not Rows("396:401").Hidden
Rows("403:408").Hidden = Not Rows("403:408").Hidden
Rows("410:415").Hidden = Not Rows("410:415").Hidden
Rows("417:422").Hidden = Not Rows("417:422").Hidden
Rows("424:429").Hidden = Not Rows("424:429").Hidden
Rows("431:436").Hidden = Not Rows("431:436").Hidden
Rows("438:443").Hidden = Not Rows("438:443").Hidden
Rows("445:450").Hidden = Not Rows("445:450").Hidden
Rows("452:457").Hidden = Not Rows("452:457").Hidden
Rows("459:464").Hidden = Not Rows("459:464").Hidden
Rows("466:471").Hidden = Not Rows("466:471").Hidden
Rows("473:478").Hidden = Not Rows("473:478").Hidden
Rows("480:485").Hidden = Not Rows("480:485").Hidden
Rows("487:492").Hidden = Not Rows("487:492").Hidden
Rows("494:499").Hidden = Not Rows("494:499").Hidden
Rows("501:506").Hidden = Not Rows("501:506").Hidden
Rows("508:513").Hidden = Not Rows("508:513").Hidden
Rows("515:520").Hidden = Not Rows("515:520").Hidden
Rows("522:527").Hidden = Not Rows("522:527").Hidden
Rows("529:534").Hidden = Not Rows("529:534").Hidden
Rows("536:541").Hidden = Not Rows("536:541").Hidden
Rows("543:548").Hidden = Not Rows("543:548").Hidden
Rows("550:555").Hidden = Not Rows("550:555").Hidden
Rows("557:562").Hidden = Not Rows("557:562").Hidden
Rows("564:569").Hidden = Not Rows("564:569").Hidden
Rows("571:576").Hidden = Not Rows("571:576").Hidden
Rows("578:582").Hidden = Not Rows("578:582").Hidden
Rows("585:590").Hidden = Not Rows("585:590").Hidden
Rows("592:597").Hidden = Not Rows("592:597").Hidden
Rows("599:604").Hidden = Not Rows("599:604").Hidden
Rows("606:611").Hidden = Not Rows("606:611").Hidden
Rows("613:618").Hidden = Not Rows("613:618").Hidden
Rows("620:625").Hidden = Not Rows("620:625").Hidden
Rows("627:632").Hidden = Not Rows("627:632").Hidden
Rows("634:639").Hidden = Not Rows("634:639").Hidden
Rows("641:646").Hidden = Not Rows("641:646").Hidden
Rows("648:653").Hidden = Not Rows("648:653").Hidden
Rows("655:660").Hidden = Not Rows("655:660").Hidden
Rows("662:667").Hidden = Not Rows("662:667").Hidden
Rows("669:674").Hidden = Not Rows("669:674").Hidden
Rows("676:681").Hidden = Not Rows("676:681").Hidden
Rows("683:688").Hidden = Not Rows("683:688").Hidden
Rows("690:695").Hidden = Not Rows("690:695").Hidden
Rows("697:701").Hidden = Not Rows("697:701").Hidden
Application.ScreenUpdating = True
End Sub


--------------------------------------------------
Excel 2007 and Windows XP Professional


Hi,

I was wondering if was possible to create a conditional calculation excluding hidden columns.

For example in C4:HA4 the cells may have a number of values L, S, U etc.

When a user access the spreadsheet certain columns a hidden based on comparing their PC user ID to an access list in the workbook.

What I would like to be able to do would be to count the instances of L in C4:HA4 but exluding the hidden columns.

I'd hoped I might be able to use SUBTOTAL in a similar way to Domenic came up with he

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1)),--(C2:C100

First off, I'm new to this forum as well to using Macros / VBA.

I've created an excel spreadsheet, and I want to hide rows where the value of a specified column = 0.

I can do this easily with the AutoFilter features, unfortunately, it leaves the pulldown arrows and this screws up any printing of the spreadsheet.

I've created two basic VBA subroutines that do the same thing, but I want to assign these to two buttons I can put on the spreadsheet so other users can also Hide / Unhide the data as needed.

My subroutines a

Sub hide()
Dim count As Integer
For count = 5 To 125
If Cells(count, 11).Value < 1 Then
Rows(count).Hidden = True
End If
Next
End Sub

Sub unhide()
Dim count As Integer
For count = 5 To 125
If Cells(count, 11).Value < 1 Then
Rows(count).Hidden = False
End If
Next
End Sub

As you can see, very basic. I can't seem to find a way to assign these or call these routines via a button in the spreadsheet that other users can activate.

Any help would be appreciated, and again, thanks in advance,

Ralph


This is what i have for trying to update the formulas when new rows are
added:

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
8).FormulaR1C1 = _ "=SUM(H2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
8) & ")"

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
9).FormulaR1C1 = _
"=SUM(I2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
9) & ")"

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
10).FormulaR1C1 = _
"=SUM(J2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
10) & ") +" _
& Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
6).Value

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

i know everybody helps when they have time so, even though i'd like to
say i need the help ASAP, i understand its never reasonable, so, when
you have time it would be wonderful




This is what i have for trying to update the formulas when new rows are
added:

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
8).FormulaR1C1 = _ "=SUM(H2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
8) & ")"

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
9).FormulaR1C1 = _
"=SUM(I2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
9) & ")"

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
10).FormulaR1C1 = _
"=SUM(J2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
10) & ") +" _
& Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
6).Value

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

i know everybody helps when they have time so, even though i'd like to
say i need the help ASAP, i understand its never reasonable, so, when
you have time it would be wonderful




This is what i have for trying to update the formulas when new rows are
added:

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
8).FormulaR1C1 = _ "=SUM(H2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
8) & ")"

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
9).FormulaR1C1 = _
"=SUM(I2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
9) & ")"

Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
10).FormulaR1C1 = _
"=SUM(J2:" & _
Worksheets("Data").Cells((Worksheets("Data").UsedRange.Rows.Count - 1),
10) & ") +" _
& Worksheets("Data").Cells(Worksheets("Data").UsedRange.Rows.Count,
6).Value

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

i know everybody helps when they have time so, even though i'd like to
say i need the help ASAP, i understand its never reasonable, so, when
you have time it would be wonderful




Hi all,

I'm trying to add the formula below to row1 if there is a value in row2/ After it inputs the fomula i need it to calculate and then paste values...

"=R[1]C&""-""&R[2]C"

to row1 if there is a value in row two...
after it inputs the fomula i need it to calculate and then paste values...

I've tried two dif ways and neither works...please help



________________________________________________________
Sub Fill_Right2()

'The option below fills across row1 all together, so its not counting my blanks

Range("A1").Formula = "=R[1]C&""-""&R[2]C"

Dim Row_1 As Long
Row_1 = Range("B2:XFD" & Range("B2").End (xlToRight).Row).Rows.Count
Range("A1" & ":XFD" & Row_1).FillRight
ActiveSheet.Calculate
Rows("1:1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
_______________________________________________________

'This option isn't working at all, I tried reusing diff code, but no luck. I get a runtime error13 type mismath....

Sub Fill_Right1()

Dim LC As Integer, i As Integer
LC = Cells(2, Columns.Count).End(xlToLeft).Column
For i = 1 To LC
If Cells(2, i).Value = Not "" Then 'it does not like this line
With Range(Cells(1, i))
.Formula = "=R[1]C&""-""&R[2]C"
.Calculate
.Value = .Value
End With
End If
Next i
End Sub


When using auto filters. (1) Date to Date then (2) Product ID I show visible rows that I can total the columns (Total Weight) that excludes non visible lines using =SUBTOTAL(9,Q10:Q1000). Works.

Additionally I need to count ONLY the number of visible cells in (Column P) the visible columns that contain the text of various Truck Companies to get a count of the number of trucks shipped. If I try to count the non blank cells of the visible cells it counts all the hidden cells too. How do I count just the number of visible cells containing text in a column when in auto filter? OR count someother column containing numbers or text for the purpose of getting a count of the visible lines that would get the same result (number of shipments)?


Hi. I want columns on one of my sheets to hide starting with the first blank one until the rest are hidden. I got my rows to hide in the same fashion with the following:

Code:

FirstBlankRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range(Cells(FirstBlankRow, "A"), Cells(Rows.Count, "A")).EntireRow.Hidden = True


But I couldn't get a version working for the columns. Any help would be appreciated!


Hi,

I am trying to see if I can do the following in Excel......

Column A
Row1 5/09/2008 8:28
Row2 5/09/2008 8:28
Row3 5/09/2008 8:28
Row4 5/09/2008 8:28

Column B
Row1 is empty
Row2 is empty
Row3 RWLU214846
Row4 RWTU9711617

Column C
Row1 RWTU9627105
Row2 CRXU0778382
Row3 is empty
Row4 is empty

Column D
Row1 5/09/2008 8:48
Row2 5/09/2008 8:48
Row3 5/09/2008 8:48
Row4 5/09/2008 8:48

Column E
Row1 VSW640
Row2 VSW640
Row3 VSW640
Row4 VSW640

Column F
Row1 5814851
Row2 5814851
Row3 5814851
Row4 5814851

I am after a formula that will give the following results in Column H, I

The above data may be 200 or more rows, with different data, but most times there will be 2, 3 or 4 rows, with data in Column F being identical. This is the reference point.

Take Column D from Column A result Column H, ie in this case 00:20 minutes

As there are 4 rows that have an identical number in Column F, count the cells in Columns B & C that fall in the same 4 rows that have data in, result in Column I. ie in this case 4

Any Help appreciated

Cfer


Guys,

I need some vba code to
1)count along a row the number of VISIBLE populated cells and
2) If the number of visible cells is = 13 then it will
3)Hide the 2nd columns data
I tried using this just for the count mechanism and testing by manually hiding the 2nd column, Range(b:b) at that time but the count cannot pass the hidden cell despite the special cells code.

so at the moment i am playing around with the following

VB:

 
ActiveSheet.UsedRange.Select 
ColcCount = Selection.SpecialCells(xlCellTypeVisible).Columns.Count 
If ColCount = "13" Then 
    Range("A1").Offset(0, 1).Select 
    Selection.EntireColumn.Hidden = True 
End If 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




The logic seems right, but its probably something really really simple for you guys!!!
Thanks in advance

When using auto filters. Sorting by (1) Date to Date then by (2) Product ID, I show visible rows that I can total the columns (Total Weight) that excludes non visible lines using =SUBTOTAL(9,Q10:Q1000). That Works.

But additionally I need to count ONLY the number of items in the visible column that contain the text of various Truck Companies to get a count of the number of trucks shipped. If I try to count the non blank cells of the visible cells it counts all the hidden cells too.

How do I count just the number of visible cells containing text in a column when in auto filter? OR count someother column containing numbers or text for the purpose of getting a count of the visible lines that would get the same result (number of shipments)?


I have a worksheet that populates cells via a VLOOKUP function.

On each column I use the SUBTOTAL function to count the number of rows with data in them =SUBTOTAL(3,A1:A1000)

The VLOOKUP function returns "0" if a field is blank in the source data and this is being counted by the SUBTOTAL function.

Is there a way I can count the rows and exclude anything with "0" in it? Note that the cells contain numbers and text so I need to exclude "0" only.


Hi all,

I have another question to ask, that is why I am posting another thread. I have a column with blanks and data (roughly abt 300 rows as such, not unique, there are many rows repeated numbers). How can I copy the distinct values from this column to another sheet. I am able to copy unique values to another column in the same sheet. But when I try to copy them to another sheet it gives me an error. I used advanced filter option to do this. I also want to get the count of these unique values. In sql I know it can be acheived with "Select Distinct row1, count(row1) from table1 order by row1". Can u help how to do this in excel.

Thanks a lot in advance,
Nitu


How do you count the hidden rows in a workbook? I know I have to use a For Each loop and I also know that the row in quesiton will have a property called hidden. However, if you loop through cells in the workbook, you will repeatedly count the same row i.e.

Cell.EntireRow = Hidden

Instead I want to loop through entire rows instead of cells

Does anyone know how to do this?

Thnx


Hi: I need to hide an entire row if cell values in two non-adjacent columns are zero.

Worksheet has data in Columns A through L

Columns E and L contain dollar figures in rows 52 through 77. The cells are pre-loaded with zero dollars with anticipation that some of the zeros will be changed by the user.

If BOTH the values in Column E & L remain as Zero, I want to Hide the entire row. The Macro below stops working after it looks at the first row and will not continue to loop.

Any ideas? I think my If then statement is wrong???

Sub HideRows()
With Range("E52:E57", "L52:L57")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

Note that if I were only looking at Column E without a need to column L, the above macro will work with a change to the Range at ("E52:E77"), but when I ask Excel to analyze Two Columns, E & L, or two values in the same row, I have the issue.

Help is appreciated

Hi All

I have a worksheet with hidden rows and a certain criteria in some of the cells in column I, my data starts from row 8 down to variable rows count. I need a VBA code that can select 25 visible rows starting from row 8 and copy them, but the selection should exclude the hidden rows and also exclude the rows whereby the cells in column I are empty
I'd appreciate any help on this
I'm using Microsoft Excel 2007 on windows 7
Kind Regards
Moh'd A.H.


I have done a search for my answer, but can't find just the right answer for my problem.
I have 4 separate macros that, individually, work well. When I add a second one to the first I get "Ambiguous name detected". After reading some of the messages, I realize that I have 4 worksheet_change(s).
These are my 4 macros:


[MACRO #1 - TARGET CELL B17, VALUE RANGE 1-5, HIDING COLUMNS L:P, DEPENDING ON B17]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr As Variant
Dim i As Long
Dim Rng As Range
Arr = Array("Jan", "Feb") 'add extra sheet names
If Target.Cells.Count = 1 And Target.Address(0, 0) = "B15" Then

For i = 0 To UBound(Arr)

With Sheets(Arr(i))
Set Rng = .Columns("L:P").EntireColumn
Rng.Hidden = False
Select Case Target.Value
Case 1
Rng.Hidden = True
Case 2
.Columns("M:O").EntireColumn.Hidden = True
Case 3
.Columns("N:O").EntireColumn.Hidden = True
Case 4
.Columns("O:O").EntireColumn.Hidden = True
Case Else
Rng.Hidden = False
End Select
End With
Next i
End If
End Sub


[MACRO #2 - SAME TARGET CELL AS IN #1, BUT WANT IT TO ALSO HIDE ANOTHER COLUMN RANGE]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr As Variant
Dim i As Long
Dim Rng As Range
Arr = Array("Jan", "Feb") 'add extra sheet names
If Target.Cells.Count = 1 And Target.Address(0, 0) = "B15" Then

For i = 0 To UBound(Arr)

With Sheets(Arr(i))
Set Rng = .Columns("AE:AI").EntireColumn
Rng.Hidden = False
Select Case Target.Value
Case 1
Rng.Hidden = True
Case 2
.Columns("AF:AH").EntireColumn.Hidden = True
Case 3
.Columns("AG:AH").EntireColumn.Hidden = True
Case 4
.Columns("AH:AH").EntireColumn.Hidden = True
Case Else
Rng.Hidden = False
End Select
End With
Next i
End If
End Sub


[MACRO #3 - DIFFERENT TARGET CELL(with values 1-10), WITH DIFFERENT COLUMN RANGE]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr As Variant
Dim i As Long
Dim Rng As Range
Arr = Array("Jan", "Feb") 'add extra sheet names
If Target.Cells.Count = 1 And Target.Address(0, 0) = "B17" Then

For i = 0 To UBound(Arr)

With Sheets(Arr(i))
Set Rng = .Columns("R:AA").EntireColumn
Rng.Hidden = False
Select Case Target.Value
Case 1
Rng.Hidden = True
Case 2
.Columns("S:Z").EntireColumn.Hidden = True
Case 3
.Columns("T:Z").EntireColumn.Hidden = True
Case 4
.Columns("U:Z").EntireColumn.Hidden = True
Case 5
.Columns("V:Z").EntireColumn.Hidden = True
Case 6
.Columns("W:Z").EntireColumn.Hidden = True
Case 7
.Columns("X:Z").EntireColumn.Hidden = True
Case 8
.Columns("Y:Z").EntireColumn.Hidden = True
Case 9
.Columns("Z:Z").EntireColumn.Hidden = True
Case Else
Rng.Hidden = False
End Select
End With
Next i
End If
End Sub


[MACRO #4 - SAME TARGET CELL AS IN #3, BUT DIFFERENT COLUMN RANGE]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr As Variant
Dim i As Long
Dim Rng As Range
Arr = Array("Jan", "Feb") 'add extra sheet names
If Target.Cells.Count = 1 And Target.Address(0, 0) = "B17" Then

For i = 0 To UBound(Arr)

With Sheets(Arr(i))
Set Rng = .Columns("AK:AT").EntireColumn
Rng.Hidden = False
Select Case Target.Value
Case 1
Rng.Hidden = True
Case 2
.Columns("AL:AS").EntireColumn.Hidden = True
Case 3
.Columns("AM:AS").EntireColumn.Hidden = True
Case 4
.Columns("AN:AS").EntireColumn.Hidden = True
Case 5
.Columns("AO:AS").EntireColumn.Hidden = True
Case 6
.Columns("AP:AS").EntireColumn.Hidden = True
Case 7
.Columns("AQ:AS").EntireColumn.Hidden = True
Case 8
.Columns("AR:AS").EntireColumn.Hidden = True
Case 9
.Columns("AS:AS").EntireColumn.Hidden = True
Case Else
Rng.Hidden = False
End Select
End With
Next i
End If
End Sub


I just don't know the correct syntax to combine these 4, for I want all 4 macros to work at the same time. Any suggestions would be greatly appreciated!
(sorry, could not get the indents to copy over..)
Steve


Hi
Can someone please tell me what is wrong with this code

R= Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count
MsgBox R

It seems to count the number of rows accurately if there are no hidden rows - or is that a given? The moment I hide a row I get a wrong count


I'm creating a macro that will check each row to determine the values
of three separate columns. If any of those three values are something
other than zero, then the macro will not Hide the row. Otherwise, if
all three values are zero, then the macro will hide the row.

The catch to this one is that 1) the cells we're checking are
formula-driven, and 2) the cell values that we're testing are typically
displayed in percentage terms. I'm having difficulty getting the value
to pass through the testing subroutine. I'm not sure if I should be
trying to convert the formula value to text first, and then test it, or
if there's a better way.

Here is a sample of a couple of different ways I've tried this:

----------------------
First Attempt


Dim TestRows As Integer
Dim count As Integer

For TestRows = 18 To 25
count = 0
If cells(testrows, 3).value <> 0 then count = count +1
If cells(testrows, 7).value <> 0 then count = count +1
If cells(testrows, 11).value <> 0 then count = count +1

if count > 0 Then
Cells(TestRows, 3).EntireRow.Hidden = False
Else
Cells(TestRows, 3).EntireRow.Hidden = True
End If

Next TestRows

--------------------------------

Second Attempt

Dim TestRows As Integer
Dim CellValue3 As String
Dim CellValue7 As String
Dim CellValue11 As String

' Check and store amounts shown in rows 18 - 25 in columns 3,
7, and 11

For TestRows = 18 To 25
CellValue3 = Cells(TestRows, 3).Text
CellValue7 = Cells(TestRows, 7).Text
CellValue11 = Cells(TestRows, 11).Text

' Print message boxes to show values for troubleshooting
MsgBox ("row " & TestRows & "column 3 = " & CellValue3)
MsgBox ("row " & TestRows & "column 7 = " & CellValue7)
MsgBox ("row " & TestRows & "column 11 = " & CellValue11)
If CellValue3 <> "0" And CellValue3 <> "0%" And CellValue7
<> "0"_
And CellValue7 <> "0%" And CellValue11 <> "0" And
CellValue3 <> "0%" Then
Cells(TestRows, 3).EntireRow.Hidden = False
Else
Cells(TestRows, 3).EntireRow.Hidden = True
End If

Next TestRows

------------------------------

Neither of these attempts have worked. I'm sure my problem is
somewhere in my variable types, but I'm not sure about what's wrong or
how to make it all work, and work efficiently. I'd appreciate any
suggestions you have!




Hello all,

I'm working on a macro that hides the rows selected/highlighted. For example, if I highlight with the mouse a3:g6 and a10:g15, only cells a3:g6 will be hidden with my code below... I want all selected rows hidden, but it doesn't seem to work... here is what I have


Code:

Sub Test()
   Dim J As Integer

    If Selection.Rows.Count > 1 Then
     For J = 1 To Selection.Rows.Count
                Selection.Rows(J).EntireRow.Hidden = True
                Next J
    End If
    

End Sub