Removing rows from a range, and adding them back later

[Alex Barrosse]

Hi all,

I've got a macro set up that opens two workbooks, copies and pastes a column containing department codes from workbook 1 into workbook 2, sheet 2, like so:

<TABLE style="WIDTH: 108pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=144><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=52 width=64>ABC_XYZ </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=80></TD></TR><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=52 width=64>BCD_XYZ</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=80></TD></TR><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=52 width=64>CDE_XYZ </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=80>Y </TD></TR><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=52 width=64>DEF_XYC </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=80></TD></TR><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=52 width=64>EFG_XYZ </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=80>Y/Exit code </TD></TR><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=52 width=64>FGH_XYZ </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=80>Y/Exit code </TD></TR><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=52 width=64>GHI_XYZ </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=80></TD></TR></TBODY></TABLE>

I basically need to find all the rows that have a "Y" or "Y/Exit code" in column B and delete/hide those rows temporarily because I'm going to run another piece of code that sums a bunch of values on sheet 1 based on matches made to the above department codes and pastes them on sheet 2 in column C next to their respective codes.

I don't need to return the values for the rows with the "Y"'s (though they do exist), hence the need for deleting them, but when I copy and paste all the values that will be in column C back to workbook 1, I need those rows back so the values match up with their original locations rather than being 5 or 10 rows short.

If I did a poor job explaining, please let me know and I'll provide more examples.

I appreciate any thoughts. Thanks.

 

[villy]

Why there's a need to delete or hide the rows if you can just disregard it in in the matching of datas?

 

[Peter_SSs]

From what you have described, I don't see a need to delete/reinstate the rows either. As villy has suggested, your second piece of code could be set up to by-pass the rows with "Y" or "Y/Exit code".

Another option would be to have your second piece of code AutoFilter this column to hide the "Y" or "Y/Exit code" rows and then just perform its operations on the visible rows then remove the AutoFilter again.

Are all the rows that do NOT have "Y" or "Y/Exit code" in that second column blank?

 

[Alex Barrosse]

Yes, all the rows that do not have the "Y" in the 2nd column are blank.

The thing is that when I copy and paste the column of codes into the new sheet, I create an array based on the values in the range and then run application.match for a range of about 6500 rows on a different sheet to the array I just created. There IS data that exists for the rows with Y's but I don't want to return that data. I just would like to have an automated way to filter out the rows with the Y's, run the match, unfilter and then copy and paste the column back into the original worksheet.

The number of rows is dynamic and will always be changing, as will the rows with Y's.

 

[Alex Barrosse]

Also, if you have any tips on how to set up the code to bypass the rows with "Y" values, that would be really helpful. This is the code I currently have:

Dim count, Row, ColumnCount, x, UsageSum, CellsDown As Long
Dim vntAnswer, FindString As String
Dim MatchCell As Range

CellsDown = ActiveCell.CurrentRegion.Rows.count

Set trimRng = Range(Cells(1, 1), Cells(CellsDown, 1))
trimRng.Value = Evaluate("IF(ISTEXT(" & trimRng.Address & "),TRIM(" & trimRng.Address & "),REPT(" & trimRng.Address & ",1))")

DestWbk.Activate
Sheets("Usage (raw)").Select
Range("A2").Select
count = ActiveCell.CurrentRegion.Rows.count
ColumnCount = ActiveCell.CurrentRegion.Columns.count

For Row = 2 To count[INDENT]For x = 1 To ColumnCount[/INDENT][INDENT][INDENT]vntAnswer = ActiveSheet.Cells(Row, x)
res = Application.Match(vntAnswer, trimRng, 0)
If Not IsError(res) Then
FindString = vntAnswer[/INDENT][/INDENT][INDENT]With trimRng
If FindString <> "" Then
UsageSum = ActiveSheet.Cells(Row, 2).Value[/INDENT]

[INDENT]Set MatchCell = trimRng.Find(What:=FindString, _ 
After:=.Cells(.Cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)[/INDENT]

[INDENT]If Not MatchCell Is Nothing Then 
MatchCell.Offset(0, 1).Value = (MatchCell.Offset(0, 1).Value + UsageSum)[/INDENT]End If
End If
End With
End If
Next x
Next Row

It currently just includes all the rows in the range of department codes but if you have a way I could put in a bypass to ignore the rows that have "Y" in column B, that would be great. Appreciate any ideas.