none
Question about find column and copy rows RRS feed

  • Question

  • Hello

    I need help to changing my macro !! 

    I sent that and I want to chang coping. . .  section in macro to as I want :

    Instead of giving range to copy cells that i want , I need to write a code (in coping. . .  section of macro)  to find that rows and just copy them. it means I have many rows there and I should find rows with just one criteria for example , the criteria is "PerturbationNumber" , if it is equel 2 , the code copy all rows that has "PerturbationNumber=2" and I put your code instea of my code that is giving range .

    Sub Macro1()
    '
    ' Macro1 Macro
    ' 2006Oct
    ' Select files . . .

        Windows("2006Oct.xlsx").Activate
        Sheets("31.75-48").Select
    ' Sorting . . .

            Range("F2").Select
        ActiveWorkbook.Worksheets("31.75-48").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("31.75-48").Sort.SortFields.Add Key:=Range( _
            "F2:F1551"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("31.75-48").Sort
            .SetRange Range("A1:I1551")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    ' Coping . . .
            Range("A2:F32").Select
            Selection.Copy
    ' Select Output file . . .
        Windows("Output.xlsx").Activate
        Sheets("1-10").Select
    ' Paste Special . . .
            Range("A1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    End sub 

    THANKS for your ATTENTION

    Sunday, February 1, 2015 11:12 AM

Answers

  • Hi Majid,

    >> if it is equel 2 , the code copy all rows that has "PerturbationNumber=2" and I put your code instea of my code that is giving range <<

    According to the description, you want to copy all the rows without hard code. We can use get the rows via usedrange.rows.count dynamically. Then we can change the code like below:

    Dim rowCount As Integer
    rowCount = ActiveSheet.UsedRange.Rows.Count
    
    ...
    Range("A2:F" &rowCount).Copy
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 2, 2015 8:29 AM
    Moderator

All replies

  • Hi Majid,

    This should help you identifying en copying the lines with a 2.

    First check if the line is filled with "2", then select, copy and paste. Then go to the next one, if it does not match a "2" it wil go to the next ine.

    dim i as integer
    
    i=0
    
    WHILE ACTIVECELL.OFFSET<>""
    
    If ActiveCell.Offset(i, O) = 2 Then
        Range(ActiveCell.Offset(i, COLUMNNUMBER), ActiveCell.Offset(i, COLUMNUMBER LAST ONE)).Select
    Selection.copy
    Selection.paste --where it needs to be pasted
        End With
         i = i + 1
    Else
        i = i + 1
    End If
    Wend
    



    Reshma

    Sunday, February 1, 2015 7:43 PM
  • Hi Majid,

    >> if it is equel 2 , the code copy all rows that has "PerturbationNumber=2" and I put your code instea of my code that is giving range <<

    According to the description, you want to copy all the rows without hard code. We can use get the rows via usedrange.rows.count dynamically. Then we can change the code like below:

    Dim rowCount As Integer
    rowCount = ActiveSheet.UsedRange.Rows.Count
    
    ...
    Range("A2:F" &rowCount).Copy
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 2, 2015 8:29 AM
    Moderator
  • THANK YOU SO MUCH 
    Monday, February 2, 2015 1:30 PM