Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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 "Jambruins" skrev i meddelelsen ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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" wrote: 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" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
how do I put it in worksheet code? Thanks.
"Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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. "Jambruins" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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 "Jambruins" wrote: 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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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 On Fri, 25 Sep 2009 12:51:02 -0700, Jambruins wrote: 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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
The error message reads:
Run-time error '13': Type mismatch. Does this help? Thanks. "Mike H" wrote: 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 "Jambruins" wrote: 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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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. "Gord Dibben" wrote: 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 On Fri, 25 Sep 2009 12:51:02 -0700, Jambruins wrote: 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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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 "Jambruins" wrote: 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. "Gord Dibben" wrote: 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 On Fri, 25 Sep 2009 12:51:02 -0700, Jambruins wrote: 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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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" wrote: 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 On Fri, 25 Sep 2009 12:51:02 -0700, Jambruins wrote: 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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing rows not wanted...
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 On Fri, 25 Sep 2009 13:27:01 -0700, Jambruins wrote: 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. "Gord Dibben" wrote: 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 On Fri, 25 Sep 2009 12:51:02 -0700, Jambruins wrote: 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. "Mike H" wrote: 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" wrote: how do I put it in worksheet code? Thanks. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing duplicate rows | New Users to Excel | |||
Removing Blank Rows ? | New Users to Excel | |||
Removing Rows for Printing | Excel Worksheet Functions | |||
removing duplicate rows | Excel Discussion (Misc queries) | |||
Removing unwanted rows | Excel Worksheet Functions |