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...
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. |
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 |