Removing rows not wanted...

[Jambruins]

In column A I have a bunch of team names. I column G there is either the
word "PLAY" or it is empty. On a seperate sheet I would like to have all the
teams with the word "PLAY" in the corresponding G cell to be listed. For
example:

Cell A3 has Missouri in it but cell G3 is empty
Cell A5 has Wake Forest in it and cell G5 has "PLAY" in it.
Cell A7 has Illinois in it but cell G7 is empty
Cell A9 has Minnesota in it and cell G9 has "PLAY" in it.

I would like my new sheet to show
Wake Forest
Minnesota

I don't want the sheet to have empty rows like

Wake Forest

Minnesota


Thanks for the help.

 

[Per Jessen]

Hi

Select your entire table and apply an autofilter, and filter on column G for
'Non Blanks'.

Copy visible cells to other sheet.

Hopes this helps.

Regards,
Per

 

[Mike H]

Hi,

Apply a filter and sort the data Z to A which will put the blanks at the
bottom and copy and paste the top rows.

Mike

 

[Jambruins]

I can do this but the sheet is going to be updated every week and I was
hoping to have something automated so I wouldn't have to do this every week.
This will work though if nobody knows a formula to do this. Thanks.

 

[Mike H]

Hi,

I don't think filtering and copying is that onerous but if you want a macto
here's one.
Put it in as worksheet code in the sheet with your data and the relevent
rows will be copied to sheet 2

Sub Please_Copy_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = Range("G3:G" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "PLAY" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End Sub

Mike

 

[Jambruins]

how do I put it in worksheet code? Thanks.

Hi,

I don't think filtering and copying is that onerous but if you want a macto
here's one.
Put it in as worksheet code in the sheet with your data and the relevent
rows will be copied to sheet 2

Sub Please_Copy_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = Range("G3:G" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "PLAY" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End Sub

Mike

 

[Mike H]

Hi,

Right click the sheet tab that contains your data, View code and paste the
code in on the right. To run it Tap F5 while in VB editor. Or tools macro
from the worksheet

Mike

 

[Jambruins]

I get a type mismatch error when I run it. Any suggestions? The sheet is
called CALCS and I would like it to paste into a sheet called PLAYS in cell
N1. I tried changing the Sheet 2 reference to PLAYS but I got the same
error. Thanks.

 

[berniean]

You can use the Data/Import External Data menu and define a query on your new
tab that is based on the data in the team tab. You would restrict the query
by applying a filter that looks for "PLAY" in column G. Each time the team
tab was updated, you would re-run your query on your "Play" tab.

 

[Mike H]

Hi,

Vhanging the sheet 2 ref to plays was the correct thing to do and the code
goes in the CALCS sheet but I can't replicate a type-mismatch error with this
code, which line does it crash out on

Sub Please_Copy_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = Range("G3:G" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "PLAY" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets("Plays").Range("A1")
End If
End Sub

Mike

 

[Gord Dibben]

Why don't you store the macro in a General module where it should be instead
of in a sheet module which are more commonly used for event type code?


Gord Dibben MS Excel MVP

 

[Jambruins]

The error message reads:

Run-time error '13':
Type mismatch.

Does this help? Thanks.

 

[Jambruins]

I am not familiar with macros at all so I am just following the advice I have
already been given. If you can explain what I should do I will certainly try
it. Thanks.

 

[Mike H]

Hi,

I ususally recommend at this stage uploading your file to savefile.com so I
can have a look but that site is down. If you mail the file to me I'll look
at it in the morning

mhughes10atsky.com

make the obvious change to the email address

Mike

 

[Mike H]

Gord,

Perhaps it's bad practice on my part but unless I'm reading in data form
multiple sheets I use worksheet code. In this case there's one paste into
another sheet that doesn't require any sheet selection so worksheet code
seems fine to me.

Mike

 

[Gord Dibben]

Alt + F11 to open VB Editor.

Right-click on your workbook/project and select "Insert Module"

Place the macro in that module.

Mike's suggestion of placing the macro in the sheet module is not "bad" in
this case.

Just non-standard practice.


Gord