reading by row depending on a number range

[rfletcher35]

Help Please!!

I have columns W - AG starting at row 5 onwards that contains my data.
Column W has numbers and these are formatted as numbers.
The sheet the data is on is called "MPR Data"

I want VBA code that will start at row 5 and work down column W selecting only rows that this column has the numbers between 358-364
I need it to select the rows within this parameter and copy cells on that row from W-AG
Then I need it to paste values on another sheet called Results Sheet starting from Column AB Row 5
I need it to do this until the last row.

I think I have explained it through but if you need more info then please ask I really need this!!!

Thanks

Fletch

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub MoveRowsForNumbers358thru364()
  Dim LastRow As Long, Addr As String
  With Sheets("MPR Data")
    LastRow = .Cells(Rows.Count, "W").End(xlUp).Row
    Addr = "'MPR Data'!W5:W" & LastRow
    With .Range(Addr)
      .Formula = Evaluate(Replace("IF((@>=358)*(@<=364),IF(@="""","""",@),""=""&@)", "@", Addr))
      Intersect(.Parent.Columns("W:AG"), .SpecialCells(xlConstants).EntireRow).Copy Sheets("Results Sheet").Range("AB5")
      .Replace "=", "", xlPart
    End With
  End With
End Sub[/td]
[/tr]
[/table]

 

[rfletcher35]

=	1224	YES	NO ABSENCE	YES	July		x	x	Stewart Aalten	727006
=	121	NO	NO ABSENCE	YES	July
=	1283	YES	Aben Aeneas	NO	May		x	x	Anita Abbott	577162
=	1582	YES	NO ABSENCE	YES	July		x	x	Michael Hampton	725468
=	1384	YES	NO ABSENCE	YES	February		x	x	Wayne Adams	725626
=	406	YES	NO ABSENCE	YES	October		x	x	Stanley Nosella	725444
=	1378	YES	NO ABSENCE	YES	February		x	x	Fahd Ahmad	728059
=	852	YES	NO ABSENCE	YES	July		x	x	Zulfikar Ahmed	725696
=	490	YES	Waqar Ahmed	NO	July		x	x	Aniel Ahmed	727752
=	975	YES	NO ABSENCE	YES	March		x	x	Rosjen Ahmed	727914

<colgroup><col style="width:48pt" width="64" span="11"> </colgroup><tbody>
</tbody>

Think that is nearly working, the above is what that code produces. until it fails but I think it fails because in my rows there are blanks.

Is there a way of telling it to ignore blanks and say run to row 4000 where I know there should be no data?

Thanks for you help

Fletch

 

[rfletcher35]

Hi,

I have just thought of a possible alternative method. Is there any way within VBA of filtering within those number ranges. That way I can just use code to copy that range of data it produces to copy which may or may not be easier, what do you think...?

So filter 358-364 on column W possibly?


Fletch

 

[rfletcher35]

Hi,

This is what I mean't by last message but it just doesn't seem to be working

ActiveSheet.Range("$V$4:$AD$3150").AutoFilter Field:=1, Criteria1:=">=358", Operator:=xlFilterValues, Criteria2:="<364", Operator:=xlFilterValues

It filters on W but doesn't come up with anything even though I know there is a 359 in there?

 

[rfletcher35]

Hi

Thanks for all your help a combination of both worked in the end.

Many thanks

Fletch