Selecting Certain Rows & Colmns Using VB 2

[Stripester]

Hi Guys,

I have a macro that once run produces values in column H!

What I would like to do is were these values appear in column H (they can appear on any row between 2 & LRow with gaps in between)is copy columns A, B, E & F, but only the rows that are greater than "0".

Can this be done?

Thankyou for any help!

Stripe

 

[SkipVought]

WHAT?

Get Bullwinkle to please explain! ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stripester]

If a code can be written it would do this:

If H2 = 20 and H13 = 4 and H100 = 2560, then the code would select:
A2 to B2, E2 to F2
A13 to B13, E13 to F13
A100 to B100, E100 to F100

Thankyou

 

[RobBroekhuis]

Ever so crudely, you could write this as

dim cell as range
set cell=range("H65535&quot.end(xlUp)
do while cell.row>1
if cell>0 then
cell.offset(0,-7).copy cell.offset(0,-6)
cell.offset(0,-2).copy cell.offset(0,-1)
endif
if cell.offset(-1,0)="" then
set cell=cell.end(xlUp)
else
set cell=cell.offset(-1,0)
endif
loop

This assumes you have a column header in H1



Rob

 

[RobBroekhuis]

Sorry, the line

cell.offset(0,-2).copy cell.offset(0,-1)

should be

cell.offset(0,-3).copy cell.offset(0,-2)



Rob

 

[Zathras]

Is this what you want (If not, please provide more examples and a better explanation):
[blue]

Sub SpecialSelect()
Dim c As Range
Dim t As Range
Dim s As Range

  
  For Each c In Intersect(ActiveSheet.UsedRange, Range("H:H"))
    If c.Value > 0 Then
      Set t = Intersect(c.EntireRow, Union(Range("A:B"), Range("E:F")))
      If s Is Nothing Then
        Set s = t
      Else
        Set s = Union(s, t)
      End If
    End If
  Next c
  If s Is Nothing Then
    MsgBox "No non-zero entries were found in column H"
  Else
    s.Select
  End If
  Set s = Nothing
  Set t = Nothing
End Sub

[/color]

 

[TonyJollans]

Hi Stripester,

You can do this kind of copy with a Filter ..

Columns("H:H").AutoFilter Field:=1, Criteria1:=">0"
Range("A2:B" & Lrow + 1, "E2:F" & Lrow + 1).Copy Destination:=Range("

Sheet3!L55

")
Columns("H:H").AutoFilter

assuming your row variable Lrow represents the end of your data (and where you supply your own destination range).

Enjoy,
Tony

 

[Stripester]

Cheers Rob,

Your code goes to all the cell that I want, but I want to select them all at once and then copy them!

Stripe

 

[Stripester]

Brilliant,

That's exactly what I wanted Zathras - works a treat!

Tried yours Tony, but had to alter it slightly:

LRow = Range("a65536&quot.End(xlUp).Row
Columns("H:H&quot.AutoFilter Field:=1, Criteria1:=">0"
Range("A2:B" & LRow, "E2:F" & LRow).Copy

but it copying all column from A to F?

Thanks

Stripe

 

[TonyJollans]

Hi Stripe,

Interesting. I must remember to test and post EXACTLY the same thing. How you specify the range seems to make a difference. Instead of ...

Range("A2:B" & LRow, "E2:F" & LRow).Copy

... try ...

Range("A2:B" & LRow & ", E2:F" & LRow).Copy

Enjoy,
Tony

 

[RobBroekhuis]

Ah, I misread - I thought you were copying E to F, etc.


Rob