Cell Selection Question

[rjacmuto32]

I have a table which the amount of rows changes each month. I have about 10 columns on it always the same. Column A is a project ID. Sevearl rows can contain the same project id.

I want to keep my file size down. Is there any way to copy say Columns C-H anytime Column A contains a certain project ID? I tried to record the macro but it puts the row number in the macro. However my row number varies depending on the project id. How do I ignore the row number.

Project ID = 977 is on say 3 rows in my table (they are in consecutive order). How can I copy columns C-H for those 3 rows and paste into another sheet?

 

[Trevor G]

What about searching down the column and then copy the entire row. Then it loops through and repeats this until all ID values have ben found.

Sub cpy()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("A" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Sheets("Action Plan").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End With
End Sub

 

[Rick Rothstein]

If your ID's are constants, then here is anothe method to consider...

Sub GetIDdata()
  Dim ID As Variant, Destination As String
  ID = 977
  Destination = "Sheet6"
  With Columns("A")
    .Replace ID, "=" & ID, xlWhole
    .SpecialCells(xlFormulas).EntireRow.Copy Worksheets(Destination).Range("A1")
    .Replace "=", "", xlPart
  End With
  With Worksheets(Destination)
    .Columns("I").Resize(, Columns.Count - 8).Delete
    .Columns("A:B").Delete
  End With
End Sub

 

[rjacmuto32]

For the 2nd one above, instead of the whole row how do you do only columns C-H. The whole row has some data I don't want displayed on the summary sheet. Just those columns.

 

[rjacmuto32]

OK I was able to modify the first reply above to this code to only copy certain columns

Sub cpy()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("A" & i).Value = "CITI-PROJECT-00139-2011" Then .Range("B" & i & ":E" & i).Copy 
Destination:=Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End With

End Sub

The other part is now at the end of that code goes to sheet3, and pastes the data into Column A offsets to the next row. What if I always want to paste it into B45 and then offset every one down from there. How would code work? (There are seveal blank rows above B45 on my summary page) So I don't think row count would work?

 

[Rick Rothstein]

OK I was able to modify the first reply above to this code to only copy certain columns...

Can I then assume that my assumption that the ID's were constants (not formulas) was incorrect?

 

[rjacmuto32]

Thats correct. Not constant each month. The reply above your code worked great, except it pastes it from column A and goes down. I'm stuck trying to get it to paste into cell B45 and offset down from there instead of top of COlumn A as in the code

 

[Rick Rothstein]

Thats correct. Not constant each month. The reply above your code worked great, except it pastes it from column A and goes down. I'm stuck trying to get it to paste into cell B45 and offset down from there instead of top of COlumn A as in the code

When I said "constant" I did not mean the list was unchanging... I meant is the ID (the 977 from your example) typed in by a human or the ID number appear in the cell because the cell has a formula in it. If the ID is type in, the you should give my code a try as I think it will be faster than the cell-by-cell processing used by Trevor.

 

[rjacmuto32]

It is a ID number in my data. assigned from a outside datasource. It's not typed in by a human.

I also don't want to delete rows. I'll show data for id 997 but then after that, I might want to show the data for id 554 based on the same data.

 

[Rick Rothstein]

It is a ID number in my data. assigned from a outside datasource. It's not typed in by a human.

Maybe I was too literal with that description. My actual question is... are there formulas in Column A? If not, then consider this modified version of my macro (it will not delete previously copied over data)...

Sub GetIDdata()
  Dim ID As Variant, LastRow As Long, NewLastRow As Long, Destination As String
  ID = 977
  Destination = "Sheet6"
  LastRow = Worksheets(Destination).Cells(Rows.Count, "A").End(xlUp).Row
  With Columns("A")
    .Replace ID, "=" & ID, xlWhole
    .SpecialCells(xlFormulas).EntireRow.Copy Worksheets(Destination).Cells(LastRow + 1, "A")
    .Replace "=", "", xlPart
  End With
  NewLastRow = Worksheets(Destination).Cells(Rows.Count, "A").End(xlUp).Row
  With Worksheets(Destination)
    .Cells(LastRow + 1, "I").Resize(NewLastRow, Columns.Count - 8).Clear
    .Cells(LastRow + 1, "A").Resize(NewLastRow, 2).Delete xlShiftToLeft
  End With
End Sub

Obviously, you should change the ID and Destination assignments at the beginning of the code. I can change the code to take input from you directly if that would be more convenient (it is not clear whether this will be stand-alone functionality or this code will be incorportated into other code of yours where asking for input would not be workable).

The main reason I am pushing so hard on this is I believe this will be a faster executing code (especially important if you have lots and lots of data).