Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm new to macros and hope somebody can help Trying to make a report from a datasheet that meets some conditions. I need to fetch all the data (cells) in a row e.g. that meet the condition <1 in column 10 and copy those cells/row to a new sheet. I cant find any functions i Excel so I hope a not to advanced macro which I can modify can do the trick. Thanks in advance.

share|improve this question
  • Open a copy of your excel sheet containing all the data
  • press ALT+F11 to open the VBA editor
  • open sheet1 from the left pane and insert the code below

    Sub mymacro()
      For myrow = 1 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If Cells(myrow, 8) < 1 Then
          i = i + 1
          ActiveWorkbook.Sheets(1).Rows(myrow).Copy Destination:=ActiveWorkbook.Sheets(2).Rows(i)
        End If
      Next myrow
    End Sub
  • go back to your sheet and press ALT+F8 and execute your macro

This code will run until the last used row, check if the value of 8th column is < 1 and if yes, copy the whole row to the next free row in sheet 2.

share|improve this answer
ok, thanks. I will try. Its a lot of rows with cars we have sold. In column "H" I add a "1" if the car is delivered. In every end of month I have to send a orderreserve and I then want to just fetch all the rows with cars that is not delivered and make a report to send. It's maybe a cumbersome way to do it, but i'm a novice :-) – Frank Sannes Jan 2 '13 at 21:43
marvellous :-) If I can bother you more I'm looking for the data who occurs in row 14 to 1000. maybe it will be quicker when the array is defined. Ok, I promise that I will try harder to learn macro programming my self :-) – Frank Sannes Jan 2 '13 at 22:50
But I want you to learn something :) What you want is really very simple. I give you a hint and you try it, ok? You have to edit line 2. – nixda Jan 2 '13 at 22:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.