Non blank rows in current sheet to new sheet and deleting text containing off

[excelisnotmyforte]

Hi

I need to grab all the non-blank rows in a table in a sheet(Sheet1) to another sheet (Sheet2), whilst deleting any text that contains theword ‘off’ in column G. The non-blank rows will usually be in a group set so Ijust need to remove the blanks which are at the beginning and end of the table.

From this new sheet (Sheet2), I will have other sheets thatwill use formulas relating back to Sheet2, however everytime I’ve run the macroit will return the formulas to a #REF! error. Any help would be appreciated.The formula returns as =IFERROR(INDEX(DataSheet!$D$2:$D$6, MATCH(Sheet2!#REF!,DataSheet!$C$2:$C$6,0)),"")
Thank u!!

 

[Michael M]

What macro are you currently using

Maybe this though, I'm guessing there will be moer to it than first appears....change sheet names to suit

Sub MM1()
Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
    With Sheets("Sheet2").Range("G:G")
        .Replace "off", ""
    End With
End Sub

 

[excelisnotmyforte]

Thanks Michael

Code was:

Sub CopyData()
Application.ScreenUpdating = False
Sheets("Sheet2").UsedRange.ClearContents
Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Cells(1, 1)
Sheets("Sheet2").Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

That was without deletion of rows in G.

 

[Michael M]

So you wanted the rows containing "off" to be deleted, not just remove the text ??

Sub MM1()
Sheets("Sheet2").UsedRange.ClearContents
Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
    With Sheets("Sheet2").Range("G:G")
        .Replace "off", ""
        .Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

 

[excelisnotmyforte]

Yes sorry! These rows containing 'off' should be deleted, not just the text removed (should've been clearer)

 

[Michael M]

Is the word "off" in the cell by itself OR part of a larger string

 

[excelisnotmyforte]

Hi Michael thanks so much for your help so far

So 'off' is in the cell by itself... so the user can select the text to be 'on' or 'off' in that column. If it helps also, there's another column that has the same option for the user to select text to be 'on' or 'off'..

i.e.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]RPM[/TD]
[TD]Engine[/TD]
[TD]SSG[/TD]
[/TR]
[TR]
[TD]0700[/TD]
[TD]600[/TD]
[TD]On[/TD]
[TD]Off[/TD]
[/TR]
[TR]
[TD]0800[/TD]
[TD]1200[/TD]
[TD]On[/TD]
[TD]On[/TD]
[/TR]
[TR]
[TD]0900[/TD]
[TD]1600[/TD]
[TD]On[/TD]
[TD]Off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So row 4 I would like to be deleted.. while any words containing 'off' in column SSG would have those corresponding rows deleted also (row 1 and row 3). So in total only row 2 would be the data set I would like to grab... my data set atm has about 200 rows hence why I would like it to be automated




Hopefully that makes a bit more sense?

 

[Michael M]

So this ???

Sub MM1()
Sheets("Sheet2").UsedRange.ClearContents
Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
    With Sheets("Sheet2").Range("G:G")
        .Replace "off", ""
        .Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .Columns("G").SpecialCells(xlCellTypeBlanks).EntireRow.Delete    
End With
End Sub