0

Just getting my feet wet with VBA for Excel. Looked around for similar issues and couldn't find anything exactly like this.

Pretty simple issue. I have some data and want to copy a section of the rows to another spot in the sheet. I don't think it'll let me post an image yet, so let's say it looks like this:

 Column A     Column B     Column C
 product1        1          #VALUE!
 product2        4            100
 product3        22            25
 product4        0             3

The rows I'd want to copy are ones where Column C is 5+. So, rows 3 and 4 in this case. The total number of rows should always be the same, if that helps.

I can do the copying and pasting in VBA, just wondering what the best way to select these rows would be. I tried a Do Loop with x being the column number, checking it was ">4", but I can't get it to work.

Thanks very much!

LeeLK
  • 39
  • 8
  • You don't typically find exactly what you're looking for :) have a look at: http://stackoverflow.com/q/12177125/212869 – NickSlash Apr 25 '13 at 17:19
  • Thanks @NickSlash I suppose you're right. I'll look through that one and see if I can adopt it to my setup! – LeeLK Apr 25 '13 at 17:25
  • Post the code you have tried? That would help others so they can revise it to better suit your specific requirements. – David Zemens Apr 25 '13 at 17:30
  • @DavidZemens I think sous2817 got me on the right track. If I can't figure it out after messing with it for a bit, I'll post what I'm trying and ask for more help. Hopefully I can handle it from here. Thanks! – LeeLK Apr 25 '13 at 17:48

1 Answers1

0

There are a ton of ways to go about it. Here's one:

Sub Macro1()

Dim LR As Long

LR = ActiveSheet.UsedRange.Rows.Count
    Range("A1:C1").Range("$A$1:$C$" & LR).AutoFilter Field:=3, Criteria1:=">4", _
        Operator:=xlAnd
    Range("A1:C" & LR).SpecialCells(xlCellTypeVisible).Copy Destination:=Range("L1")
    Range("A1:C1").Range("$A$1:$C$" & LR).AutoFilter
End Sub

Adjust Destination range to suit.

sous2817
  • 3,915
  • 2
  • 33
  • 34
  • Thanks @sous2817. That is definitely getting me on the right track. I don't want to take up ya'lls time with the fine tuning (the starting range is actually A18:Q18, below some other stuff, so you wouldn't be able to count all the rows and use that as the range), so I'll mess with it and learn that way :). Thanks again! – LeeLK Apr 25 '13 at 17:46
  • depending on the structure of your sheet, there is a large handful of ways to find and define the expected range. I just gave you one way. Try googling something like VBA Excel Find Last Row for about 642,000 ways to go about it (granted, some will be more helpful than others). – sous2817 Apr 25 '13 at 17:59
  • So true. Many ways to do it, most of them I don't understand yet! I'll keep looking. – LeeLK Apr 25 '13 at 18:11
  • FYI, if anyone finds this in the future, I used a For/If statement (sorry the formatting is sloppy I thought I was doing it right but I guess not...): Dim r As Long, endRow As Long, pasteRowIndex As Long endRow = 200 pasteRowIndex = 16 For r = 18 To endRow If Cells(r, Columns("BK").Column).Value > 4 Then Rows(r).Select Selection.Cut Rows(pasteRowIndex).Select Selection.Insert Shift:=xlDown pasteRowIndex = pasteRowIndex + 1 End If Next r – LeeLK May 01 '13 at 16:55