Cell Selection Question

rjacmuto32

Board Regular
Joined
Jul 14, 2004
Messages
98
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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
If your ID's are constants, then here is anothe method to consider...

Code:
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

Board Regular
Joined
Jul 14, 2004
Messages
98
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

Board Regular
Joined
Jul 14, 2004
Messages
98

ADVERTISEMENT

OK I was able to modify the first reply above to this code to only copy certain columns
Code:
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?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jul 14, 2004
Messages
98

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jul 14, 2004
Messages
98
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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)...

Code:
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).
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top