Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
I have a list in a worksheet which has a column headings and in one column
contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks -- James |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
Dim myCell as range
dim myRng as range dim delRng as range with worksheets("Somesheetnamehere") set myrng = .range("A1", .cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells if lcase(left(mycell.value,1)) = lcase("u") then 'keep it else if delrng is nothing then set delrng = mycell else set delrng = union(mycell, delrng) end if end if next mycell if delrng is nothing then 'nothing to delete else delrng.entirerow.delete end if ======== Untested, uncompiled. Watch for typos. I used column A and deleted the entire row. You may have to change the code. James C wrote: I have a list in a worksheet which has a column headings and in one column contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks -- James -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
Sub Hide_U_Rows()
Dim RngCol As Range Dim i As Range Set RngCol = Range("B1", Range("B" & Rows.Count). _ End(xlUp).Address) For Each i In RngCol If Left(i.Value, 1) = "U" Then _ i.EntireRow.Hidden = True ' i.EntireRow.Delete End If Next i End Sub Gord Dibben MS Excel MVP On Mon, 25 Jan 2010 06:46:01 -0800, James C wrote: I have a list in a worksheet which has a column headings and in one column contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
Dave thank you for your response.
I am having difficulity with running your suggestion. I hope you may be able to still help. The with worksheets was out of range so I deleted this as I put your macro into part of a current macro.I am hoping this is not a problem. The set myrng = .range("A1", .cells(.rows.count,"A").end(xlup)) is coming up as a complie error and I do not know how to orrect this. Can you help further? Thank you James -- James "Dave Peterson" wrote: Dim myCell as range dim myRng as range dim delRng as range with worksheets("Somesheetnamehere") set myrng = .range("A1", .cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells if lcase(left(mycell.value,1)) = lcase("u") then 'keep it else if delrng is nothing then set delrng = mycell else set delrng = union(mycell, delrng) end if end if next mycell if delrng is nothing then 'nothing to delete else delrng.entirerow.delete end if ======== Untested, uncompiled. Watch for typos. I used column A and deleted the entire row. You may have to change the code. James C wrote: I have a list in a worksheet which has a column headings and in one column contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks -- James -- Dave Peterson . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
Dave hi again had a second go using a new workbook.
The macro works fine but deletes my first row which has my column headings in. Can you advise how to overcome this. Thanks -- James "Dave Peterson" wrote: Dim myCell as range dim myRng as range dim delRng as range with worksheets("Somesheetnamehere") set myrng = .range("A1", .cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells if lcase(left(mycell.value,1)) = lcase("u") then 'keep it else if delrng is nothing then set delrng = mycell else set delrng = union(mycell, delrng) end if end if next mycell if delrng is nothing then 'nothing to delete else delrng.entirerow.delete end if ======== Untested, uncompiled. Watch for typos. I used column A and deleted the entire row. You may have to change the code. James C wrote: I have a list in a worksheet which has a column headings and in one column contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks -- James -- Dave Peterson . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
Thank you for your response.
I am have copied and pasted your suggestion into my current macro and am having no luck with it working. It comes up with an error message End If Without Block If. Tried your macro in a new workbook and it appeasr to hide or delete U codes leaving the codes that I do not require. Can you help further? thanks -- James "Gord Dibben" wrote: Sub Hide_U_Rows() Dim RngCol As Range Dim i As Range Set RngCol = Range("B1", Range("B" & Rows.Count). _ End(xlUp).Address) For Each i In RngCol If Left(i.Value, 1) = "U" Then _ i.EntireRow.Hidden = True ' i.EntireRow.Delete End If Next i End Sub Gord Dibben MS Excel MVP On Mon, 25 Jan 2010 06:46:01 -0800, James C wrote: I have a list in a worksheet which has a column headings and in one column contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
First, you found that you have to change the first line to the name of the
sheet. And if you change the second line to start in A2 (not A1), you'll avoid row 1. with worksheets("Somesheetnamehere") set myrng = .range("A1", .cells(.rows.count,"A").end(xlup)) end with James C wrote: Dave hi again had a second go using a new workbook. The macro works fine but deletes my first row which has my column headings in. Can you advise how to overcome this. Thanks -- James "Dave Peterson" wrote: Dim myCell as range dim myRng as range dim delRng as range with worksheets("Somesheetnamehere") set myrng = .range("A1", .cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells if lcase(left(mycell.value,1)) = lcase("u") then 'keep it else if delrng is nothing then set delrng = mycell else set delrng = union(mycell, delrng) end if end if next mycell if delrng is nothing then 'nothing to delete else delrng.entirerow.delete end if ======== Untested, uncompiled. Watch for typos. I used column A and deleted the entire row. You may have to change the code. James C wrote: I have a list in a worksheet which has a column headings and in one column contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks -- James -- Dave Peterson . -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
Remove the space-underscore at the end of this line:
If Left(i.Value, 1) = "U" Then _ And Gord left two options in the code. You can delete the row or hide the row. The delete is commented out. Use the one you want and delete the line you don't want. James C wrote: Thank you for your response. I am have copied and pasted your suggestion into my current macro and am having no luck with it working. It comes up with an error message End If Without Block If. Tried your macro in a new workbook and it appeasr to hide or delete U codes leaving the codes that I do not require. Can you help further? thanks -- James "Gord Dibben" wrote: Sub Hide_U_Rows() Dim RngCol As Range Dim i As Range Set RngCol = Range("B1", Range("B" & Rows.Count). _ End(xlUp).Address) For Each i In RngCol If Left(i.Value, 1) = "U" Then _ i.EntireRow.Hidden = True ' i.EntireRow.Delete End If Next i End Sub Gord Dibben MS Excel MVP On Mon, 25 Jan 2010 06:46:01 -0800, James C wrote: I have a list in a worksheet which has a column headings and in one column contains text data. In the rows the data consists of items such E10, V19 and U27.The rows in the list change frequenlty, as new data is added, so the column length varies. I want to get rid of any item that does not have the prefix U in front of it and include this in part of an existing VBA statement. Thanks . -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter out rows of information in a list via VBA
Bit of a mis-read on my part.
Try this revised version. Option Compare Text Sub Delete_NonU_Rows() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long FirstRow = 1 LastRow = Cells(Rows.Count, "B").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 If Left(Cells(iRow, "B").Value, 1) < "U" Then Rows(iRow).EntireRow.Delete End If Next End Sub Gord On Tue, 26 Jan 2010 02:54:01 -0800, James C wrote: Thank you for your response. I am have copied and pasted your suggestion into my current macro and am having no luck with it working. It comes up with an error message End If Without Block If. Tried your macro in a new workbook and it appeasr to hide or delete U codes leaving the codes that I do not require. Can you help further? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
information filter | Excel Discussion (Misc queries) | |||
Fill in rows with Information from Drop Down list | Excel Worksheet Functions | |||
how do i filter a list greater than 1000 rows | Excel Discussion (Misc queries) | |||
filter: how to print filter list options in dropdown box | Excel Discussion (Misc queries) | |||
filter and keep same information. | Excel Worksheet Functions |