Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
Hi everyone,
How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
Dim myRng as range
with worksheets("Wheel") set myrng = .range("b4", .range("b4").end(xldown)) 'I like coming from the bottom of the worksheet and going to the top. 'just in case there are gaps in column B. set myrng = .range("b4", .cells(.rows.count,"B").end(xlup)) end with Paul Black wrote: Hi everyone, How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
Ps. Then add:
msgbox myrng.rows.count or msgbox myrng.cells.count 'since it's a single column. Paul Black wrote: Hi everyone, How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
Thanks for the reply Dave,
I have added your code but it does not work. Here is what I have got :- Private Sub Test() Dim sStr As String Dim vValues As Variant Dim myrng As Range sStr = Replace(Mid(Worksheets("Wheel").Range("B2"), 4), ")=", ",") vValues = Split(sStr, ",") With Worksheets("Statistics").Range("B2").Select Set myrng = Worksheets("Wheel").Range("B4", .Range("B4").End(xlDown)) .Offset(0, 0).Value = "F" .Offset(1, 0).Value = "S" .Offset(2, 0).Value = "N" .Offset(3, 0).Value = "M" .Offset(4, 0).Value = "T" .Offset(1, 4).Value = vValues(0) .Offset(2, 4).Value = vValues(1) .Offset(3, 4).Value = vValues(2) & " if " & vValues(3) .Offset(3, 4).HorizontalAlignment = xlRight .Offset(4, 4).Value = vValues(4) .Offset(4, 5).Value = myrng.Rows.Count End With End Sub Thanks in Advance. All the Best. Paul On Sep 1, 10:02 pm, Dave Peterson wrote: Ps. Then add: msgbox myrng.rows.count or msgbox myrng.cells.count 'since it's a single column. Paul Black wrote: Hi everyone, How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul -- Dave Peterson- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
I'm confused by your code.
Maybe you could explain what you're doing and what's in B2 of the Wheel worksheet. Paul Black wrote: Thanks for the reply Dave, I have added your code but it does not work. Here is what I have got :- Private Sub Test() Dim sStr As String Dim vValues As Variant Dim myrng As Range sStr = Replace(Mid(Worksheets("Wheel").Range("B2"), 4), ")=", ",") vValues = Split(sStr, ",") With Worksheets("Statistics").Range("B2").Select Set myrng = Worksheets("Wheel").Range("B4", .Range("B4").End(xlDown)) .Offset(0, 0).Value = "F" .Offset(1, 0).Value = "S" .Offset(2, 0).Value = "N" .Offset(3, 0).Value = "M" .Offset(4, 0).Value = "T" .Offset(1, 4).Value = vValues(0) .Offset(2, 4).Value = vValues(1) .Offset(3, 4).Value = vValues(2) & " if " & vValues(3) .Offset(3, 4).HorizontalAlignment = xlRight .Offset(4, 4).Value = vValues(4) .Offset(4, 5).Value = myrng.Rows.Count End With End Sub Thanks in Advance. All the Best. Paul On Sep 1, 10:02 pm, Dave Peterson wrote: Ps. Then add: msgbox myrng.rows.count or msgbox myrng.cells.count 'since it's a single column. Paul Black wrote: Hi everyone, How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
Thanks for the reply Dave,
The Test Sub splits a string in the "Wheel" sheet into individual components and outputs the titles and components to the "Statistics" sheet. The "Wheel" sheet has numbers going from "B4" down to whatever. I would like the number of rows in the "Wheel" sheet with numbers in please. So if the rows went from 4 to 48 the answer would be 44 and go in the "Statistics" sheet in :- .Offset(4, 5).Value = Total Number of Rows Thanks in Advance. All the Best. Paul On Sep 1, 10:46 pm, Dave Peterson wrote: I'm confused by your code. Maybe you could explain what you're doing and what's in B2 of the Wheel worksheet. Paul Black wrote: Thanks for the reply Dave, I have added your code but it does not work. Here is what I have got :- Private Sub Test() Dim sStr As String Dim vValues As Variant Dim myrng As Range sStr = Replace(Mid(Worksheets("Wheel").Range("B2"), 4), ")=", ",") vValues = Split(sStr, ",") With Worksheets("Statistics").Range("B2").Select Set myrng = Worksheets("Wheel").Range("B4", .Range("B4").End(xlDown)) .Offset(0, 0).Value = "F" .Offset(1, 0).Value = "S" .Offset(2, 0).Value = "N" .Offset(3, 0).Value = "M" .Offset(4, 0).Value = "T" .Offset(1, 4).Value = vValues(0) .Offset(2, 4).Value = vValues(1) .Offset(3, 4).Value = vValues(2) & " if " & vValues(3) .Offset(3, 4).HorizontalAlignment = xlRight .Offset(4, 4).Value = vValues(4) .Offset(4, 5).Value = myrng.Rows.Count End With End Sub Thanks in Advance. All the Best. Paul On Sep 1, 10:02 pm, Dave Peterson wrote: Ps. Then add: msgbox myrng.rows.count or msgbox myrng.cells.count 'since it's a single column. Paul Black wrote: Hi everyone, How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
So it was just that little portion <bg.
Maybe something like: Option Explicit Private Sub Test() Dim myRng As Range Dim sStr As String Dim vValues As Variant With Worksheets("wheel") Set myRng = .Range("b4", .Range("B4").End(xlDown)) End With sStr = Replace(Mid(Worksheets("Wheel").Range("B2"), 4), ")=", ",") vValues = Split(sStr, ",") With Worksheets("Statistics") .Offset(0, 0).Value = "F" .Offset(1, 0).Value = "S" .Offset(2, 0).Value = "N" .Offset(3, 0).Value = "M" .Offset(4, 0).Value = "T" .Offset(1, 4).Value = vValues(0) .Offset(2, 4).Value = vValues(1) .Offset(3, 4).Value = vValues(2) & " if " & vValues(3) .Offset(3, 4).HorizontalAlignment = xlRight .Offset(4, 4).Value = vValues(4) .Offset(4, 5).Value = myRng.Rows.Count End With End Sub Paul Black wrote: Thanks for the reply Dave, The Test Sub splits a string in the "Wheel" sheet into individual components and outputs the titles and components to the "Statistics" sheet. The "Wheel" sheet has numbers going from "B4" down to whatever. I would like the number of rows in the "Wheel" sheet with numbers in please. So if the rows went from 4 to 48 the answer would be 44 and go in the "Statistics" sheet in :- .Offset(4, 5).Value = Total Number of Rows Thanks in Advance. All the Best. Paul On Sep 1, 10:46 pm, Dave Peterson wrote: I'm confused by your code. Maybe you could explain what you're doing and what's in B2 of the Wheel worksheet. Paul Black wrote: Thanks for the reply Dave, I have added your code but it does not work. Here is what I have got :- Private Sub Test() Dim sStr As String Dim vValues As Variant Dim myrng As Range sStr = Replace(Mid(Worksheets("Wheel").Range("B2"), 4), ")=", ",") vValues = Split(sStr, ",") With Worksheets("Statistics").Range("B2").Select Set myrng = Worksheets("Wheel").Range("B4", .Range("B4").End(xlDown)) .Offset(0, 0).Value = "F" .Offset(1, 0).Value = "S" .Offset(2, 0).Value = "N" .Offset(3, 0).Value = "M" .Offset(4, 0).Value = "T" .Offset(1, 4).Value = vValues(0) .Offset(2, 4).Value = vValues(1) .Offset(3, 4).Value = vValues(2) & " if " & vValues(3) .Offset(3, 4).HorizontalAlignment = xlRight .Offset(4, 4).Value = vValues(4) .Offset(4, 5).Value = myrng.Rows.Count End With End Sub Thanks in Advance. All the Best. Paul On Sep 1, 10:02 pm, Dave Peterson wrote: Ps. Then add: msgbox myrng.rows.count or msgbox myrng.cells.count 'since it's a single column. Paul Black wrote: Hi everyone, How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count the Number of Variable Rows
Brilliant Dave, it does EXACTLY as I want.
Thanks VERY much. All the Best. Paul On Sep 2, 12:41 am, Dave Peterson wrote: So it was just that little portion <bg. Maybe something like: Option Explicit Private Sub Test() Dim myRng As Range Dim sStr As String Dim vValues As Variant With Worksheets("wheel") Set myRng = .Range("b4", .Range("B4").End(xlDown)) End With sStr = Replace(Mid(Worksheets("Wheel").Range("B2"), 4), ")=", ",") vValues = Split(sStr, ",") With Worksheets("Statistics") .Offset(0, 0).Value = "F" .Offset(1, 0).Value = "S" .Offset(2, 0).Value = "N" .Offset(3, 0).Value = "M" .Offset(4, 0).Value = "T" .Offset(1, 4).Value = vValues(0) .Offset(2, 4).Value = vValues(1) .Offset(3, 4).Value = vValues(2) & " if " & vValues(3) .Offset(3, 4).HorizontalAlignment = xlRight .Offset(4, 4).Value = vValues(4) .Offset(4, 5).Value = myRng.Rows.Count End With End Sub Paul Black wrote: Thanks for the reply Dave, The Test Sub splits a string in the "Wheel" sheet into individual components and outputs the titles and components to the "Statistics" sheet. The "Wheel" sheet has numbers going from "B4" down to whatever. I would like the number of rows in the "Wheel" sheet with numbers in please. So if the rows went from 4 to 48 the answer would be 44 and go in the "Statistics" sheet in :- .Offset(4, 5).Value = Total Number of Rows Thanks in Advance. All the Best. Paul On Sep 1, 10:46 pm, Dave Peterson wrote: I'm confused by your code. Maybe you could explain what you're doing and what's in B2 of the Wheel worksheet. Paul Black wrote: Thanks for the reply Dave, I have added your code but it does not work. Here is what I have got :- Private Sub Test() Dim sStr As String Dim vValues As Variant Dim myrng As Range sStr = Replace(Mid(Worksheets("Wheel").Range("B2"), 4), ")=", ",") vValues = Split(sStr, ",") With Worksheets("Statistics").Range("B2").Select Set myrng = Worksheets("Wheel").Range("B4", .Range("B4").End(xlDown)) .Offset(0, 0).Value = "F" .Offset(1, 0).Value = "S" .Offset(2, 0).Value = "N" .Offset(3, 0).Value = "M" .Offset(4, 0).Value = "T" .Offset(1, 4).Value = vValues(0) .Offset(2, 4).Value = vValues(1) .Offset(3, 4).Value = vValues(2) & " if " & vValues(3) .Offset(3, 4).HorizontalAlignment = xlRight .Offset(4, 4).Value = vValues(4) .Offset(4, 5).Value = myrng.Rows.Count End With End Sub Thanks in Advance. All the Best. Paul On Sep 1, 10:02 pm, Dave Peterson wrote: Ps. Then add: msgbox myrng.rows.count or msgbox myrng.cells.count 'since it's a single column. Paul Black wrote: Hi everyone, How can I count the number of rows with data in from "B4" down to whatever. I would like it based on this please :- Worksheets("A").Range("B4") I have tried :- Worksheets("Wheel").Range("B4").UsedRange.Rows.Cou nt Worksheets("Wheel").Range("B4","B4" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4","B" & .End(xlDown), Rows.Count) Worksheets("Wheel").Range("B4:B" & .End(xlDown), Rows.Count) etc Thanks in Advance. All the Best. Paul -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count number of dates in a row of cel before or after a variable d | Excel Worksheet Functions | |||
Count number of rows, where non relevant rows are hidden | Excel Discussion (Misc queries) | |||
Count rows and insert number to count them. | Excel Discussion (Misc queries) | |||
count number of rows (variable range) | Excel Programming | |||
change the number of rows to a variable | Excel Programming |