Hi all -
I have an Excel data-set that contains more columns and rows than I need in my final table. I would like to extract the rows that contain the word "Domestic" in it into a new table.
For example, I have 6 columns of data (A, B, C, D, E, F) and column "F" is where the word "Domestic" would be if the cell contains it. I would then like to bring back columns A, C, and D for all rows where column F contains "Domestic" - and if the row does not contain "Domestic" it is ignored. My headers is in row 1 and my data runs from Row 2-599.
I was given the below VBA code by another user (thanks Fishboy!) - but it is returning more rows than those that contain "domestic" (not all of the rows from my original dataset, but about double than what it should be returning). Also, how do I modify this code so that I bring back columns C, D, and F from Sheet 1 into Sheet 2 (right now it the code brings back C and D via this line:
<<Copy cells C and D of the cell row to the first row of column B in sheet2>>
Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
' Increase LastRow2 by 1 to account for the new data
LastRow2 = LastRow2 + 1"
Sub CopyCells()
' Defines variables
Dim Cell As Range, cRange As Range
' Defines LastRow1 as the last row of data in sheet 1 based on column A
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastRow2 as the first blank row of data in sheet 2 based on column A
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
' Sets the check range as F2 to the last row of F on sheet1
Set cRange = Sheets("Sheet1").Range("F2:F" & LastRow1)
' For each cell in the check range
For Each Cell In cRange
' If the cell value is Domestic then...
If Cell.Value = "Domestic" Then
' Copy cell A of the cell row to the first blank row of column A in sheet2
Sheets("Sheet1").Range("A" & Cell.Row).Copy Sheets("Sheet2").Range("A" & LastRow2)
' Copy cells C and D of the cell row to the first row of column B in sheet2
Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
' Increase LastRow2 by 1 to account for the new data
LastRow2 = LastRow2 + 1
End If
' Check next cell in check range
Next Cell
End Sub
I have an Excel data-set that contains more columns and rows than I need in my final table. I would like to extract the rows that contain the word "Domestic" in it into a new table.
For example, I have 6 columns of data (A, B, C, D, E, F) and column "F" is where the word "Domestic" would be if the cell contains it. I would then like to bring back columns A, C, and D for all rows where column F contains "Domestic" - and if the row does not contain "Domestic" it is ignored. My headers is in row 1 and my data runs from Row 2-599.
I was given the below VBA code by another user (thanks Fishboy!) - but it is returning more rows than those that contain "domestic" (not all of the rows from my original dataset, but about double than what it should be returning). Also, how do I modify this code so that I bring back columns C, D, and F from Sheet 1 into Sheet 2 (right now it the code brings back C and D via this line:
<<Copy cells C and D of the cell row to the first row of column B in sheet2>>
Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
' Increase LastRow2 by 1 to account for the new data
LastRow2 = LastRow2 + 1"
Sub CopyCells()
' Defines variables
Dim Cell As Range, cRange As Range
' Defines LastRow1 as the last row of data in sheet 1 based on column A
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastRow2 as the first blank row of data in sheet 2 based on column A
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
' Sets the check range as F2 to the last row of F on sheet1
Set cRange = Sheets("Sheet1").Range("F2:F" & LastRow1)
' For each cell in the check range
For Each Cell In cRange
' If the cell value is Domestic then...
If Cell.Value = "Domestic" Then
' Copy cell A of the cell row to the first blank row of column A in sheet2
Sheets("Sheet1").Range("A" & Cell.Row).Copy Sheets("Sheet2").Range("A" & LastRow2)
' Copy cells C and D of the cell row to the first row of column B in sheet2
Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
' Increase LastRow2 by 1 to account for the new data
LastRow2 = LastRow2 + 1
End If
' Check next cell in check range
Next Cell
End Sub
