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.Count
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

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.Count
> 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

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.Count
> 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

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 <peter...@verizonXSPAM.net> 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.Count
> > 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 -

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 <peter...@verizonXSPAM.net> 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.Count
> > > 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

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 <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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.Count
> > > > 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 -

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 <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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.Count
> > > > > 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

Brilliant Dave, it does EXACTLY as I want.

Thanks VERY much.
All the Best.
Paul

On Sep 2, 12:41 am, Dave Peterson <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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.Count
> > > > > > 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 -