Counting Nonhidden Cells In A Row 


Counting Nonhidden Cells In A Row  Excel 
View Answers 
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.
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 Tutorials
Update, Change, and Manage the Data Used in a Chart in Excel
In this tutorial I am going to show you how to update, change and manage the data used by charts in Excel. This tut ...
In this tutorial I am going to show you how to update, change and manage the data used by charts in Excel. This tut ...
How to View, Arrange, or Hide All Shapes, Charts, Pictures, Etc. in Excel
In Excel, you can add shapes, images, objects, photos, and all sorts of items to the spreadsheet. The problem is th ...
In Excel, you can add shapes, images, objects, photos, and all sorts of items to the spreadsheet. The problem is th ...
Prevent a User from Changing Any Data in Excel
I'll show you how to lock a spreadsheet so that nothing can be changed in it. This is a great feature to use when ...
I'll show you how to lock a spreadsheet so that nothing can be changed in it. This is a great feature to use when ...
How to Add Boxes, Buttons, Arrows, SpeechBubbles, Hearts, and More to a Spreadsheet in Excel
In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as well as adding text to t ...
In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as well as adding text to t ...
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
 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
 This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Vlookup Macro to Return All Matching Results from a Sheet in Excel
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Excel Macro that Searches Entire Workbook and Returns All Matches
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Similar Topics
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:
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
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.
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:

Excel 2007 and Windows XP Professional
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
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
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
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
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
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
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)?
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:
But I couldn't get a version working for the columns. Any help would be appreciated!
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
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
I have two rows with values and blanks at the end (but I don't know how many blanks). For example,
Row1: A C E G * * *
Row2: H A C F I * * * *
* is blank
The values could be either text or numbers (but not both at the same time).
Each individual row has unique values (no duplicate values in each row).
1. I would like to know how many values there are in Row2 that doesn't exist in Row1, ignoring the blanks. In the example above, there are 3 values in Row2 that doesn't exist in Row1 (H, F, I).
2. Alternatively, I would like to know how many of the values in Row1 also exist in Row2 (this is actually the final result that I need and it can easily be calculated from # of values in Row2  result above = 53=2 ).
Could someone propose a formula for this?
TIA!
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:
The logic seems right, but its probably something really really simple for you guys!!!
Thanks in advance
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 IfIf 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)?
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.
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: I need to hide an entire row if cell values in two nonadjacent 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 preloaded 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
Worksheet has data in Columns A through L
Columns E and L contain dollar figures in rows 52 through 77. The cells are preloaded 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
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 15, 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 110), 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
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 15, 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 110), 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
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
formuladriven, 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!
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
formuladriven, 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!
Hi, i am trying to understand a macro i used previously and try and update the macro so i can use it now.
what this macro did, was to look at cells in row 1, and if the cell value = "Hide" the the macro would hide the entire column.
Code:
What I am looking at now, is changing this macro to look at all cells in row 4, from column A to column BZ, and if the cell value = "Hide", then the entire column will be hidden.
I can't figure out which bits of the macro are doing what. Any help would be greatly appreciated.
Thanks,
Thomas
what this macro did, was to look at cells in row 1, and if the cell value = "Hide" the the macro would hide the entire column.
Code:
Sub Hide_Columns() Dim a As Long, c As Long, d As Long, e As Long Dim b As String, f As String For a = 1 To Sheets.Count b = Worksheets(a).Name If b = "Overview" Or b = "Sheet1" Or b = "Sheet2" Then c = Worksheets(a).Cells(1, Columns.Count).End(xlToLeft).Column d = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row For e = 1 To c If Worksheets(a).Cells(1, e) = "Hide" Then f = Chr(e + 64) Worksheets(a).Range(f & 1 & ":" & f & d).EntireColumn.Hidden = True End If Next e End If Next a MsgBox "Completed" End Sub
What I am looking at now, is changing this macro to look at all cells in row 4, from column A to column BZ, and if the cell value = "Hide", then the entire column will be hidden.
I can't figure out which bits of the macro are doing what. Any help would be greatly appreciated.
Thanks,
Thomas
Hi
This should be an easy one. I have a macro that hides columns in a table based on a month range.
I need a formula that will then sum that total of all columns that are still visible. The macro to hide the columns is:
Code:
I know how to sum if it is filtered but not if the column is merely user hidden.
I have tried SUBTOTAL(109,AA8:FB8) and SUBTOTAL(9,AA8:FB8) but neither return a value at all.
Any help would be great. Thanks.
This should be an easy one. I have a macro that hides columns in a table based on a month range.
I need a formula that will then sum that total of all columns that are still visible. The macro to hide the columns is:
Code:
Set rTest = Range("AA20", Range("AA20").End(xlToRight)) For Each cl In rTest If cl.Value > Range("End_Month").Value Or cl.Value < Range("Start_Month").Value Then cl.EntireColumn.Hidden = True End If Next cl
I know how to sum if it is filtered but not if the column is merely user hidden.
I have tried SUBTOTAL(109,AA8:FB8) and SUBTOTAL(9,AA8:FB8) but neither return a value at all.
Any help would be great. Thanks.
Hi,
I am trying to figure out if a hidden group of cells can be excluded from a formula.
Basically, I have a column which counts incidents each month.
So A1  A50 has a total count of 50.
A1  A25 is March, and A26  A50 is April. So if I hide A1A25, would it be posible to have my count formula to update automatically to remove the hidden cells?
This would help me so that I could just add data each month, instead of having to copy paste and delete. + I could run reports for the whole year when needed.
Kind Regards
Colin
I am trying to figure out if a hidden group of cells can be excluded from a formula.
Basically, I have a column which counts incidents each month.
So A1  A50 has a total count of 50.
A1  A25 is March, and A26  A50 is April. So if I hide A1A25, would it be posible to have my count formula to update automatically to remove the hidden cells?
This would help me so that I could just add data each month, instead of having to copy paste and delete. + I could run reports for the whole year when needed.
Kind Regards
Colin
Hi guys,
So this is the new problem I am faced with. I work with Excel 2003. I want to do a formula that works through filters, so I did subtotal (e.g =SUBTOTAL(103,E$9:E$301) ) but, in the column that I need to count is a formula entered in each cell which is an IF/AND formula =IF(AND(F9="",D9<TODAY(),ISNUMBER(D9)),"Over due","") so the subtotal is counting all of the cells when I only need it to count the cells which display the result "over due", then I need it to work in a filter...
I can not think of a way that this is possible...then again my knowledge of Excel is basic? I can do COUNTIF (which is what I have now) but then it doesn't work in filters.
If it isn't possible then never mind I'll work round it, but I would prefer to avoid VBA where possible.
Thanks