I have a database and a user form. Lets assume the database hosts a list of all cities in North America in column C, with that city's state/prov. in column B, and in column A, that state/prov.'s country.
In the user form, the user will select from the available list in the first combobox either Canada or United States. Based on the selection from combobox1, combobox2's rowlist will contain only the states, or provinces, based on the filtered data. The database will be filtered based on column A, column B now shows only the states or provinces it the US or Canada is chosen respectively.
Based then on the selection in combobox2, the user selects from combobox3, only those cities found in that state or province ... information gathered from a filter of column A (country), column B (state).
Basically, with each combobox selection, the database is being filtered.
I've got a userform with a combobox and a textbox. The combobox is populated from a dynamic range of product numbers and when a existing number is selected from the list, a respective product name appears in the textbox. These are then saved to a database sheet via command button.
My problem is following:
When a new product number is entered in the combobox that isn't on the list, I want the number & name of the new product to be saved not only on the database sheet but also on the source sheet so that the new product would be included in the combobox list when run again. Here's my code so far:
Private Sub CommandButton1_Click()
'here is the code that saves everything to the database sheet
I am unable to reference the combobox's rowsource to another sheet dynamically The codes below are initialized when the userform is loaded. Is there anything wrong with it?
Normally the combo box will reading the data range from top to bottom. Is there any way to allow the combo box to reading the data from left to right? when i cover, it only reading the first cell of data range.
I have a UserForm with a ComboBox on it. The RowSource for this ComboBox is a named range called rInv. rInv has rows broken down by day and 105 columns broken down by items. The rows are broken bown to 31 day sections with 27 possible customer/Invoices per day. Most days have between 2 to 20 Customer/Invoices, which leaves anywhere from 7 to 25 blank entries per day. These blanks spaces won't allow the user to scroll past the 1st days Customers/Invoices!
Is there any code to remeve the empty spaces from being seen by the ComboBox RowSource? (I can't actually remove the spaces, they need to remain)
My Userform with combobox entries for product checks against the rowsource and if not on the list, is added to the list. the list is a named range in a Table that is used in a data model.
No matter which way I try I am regularly getting a corruption in the table that is home to the named range which is the rowsource... after one or two entries from the userform.
The rowsource is not bound to the worksheet, it is populated at userform_initialize procedure. (There are 4 combobox drawing from same rouwsource hence the loop through to check each)
debug points to the line at which conbobox value is being entered into the rowsource, though not always - it sometimes just crashes the whole file and everything needs restarting, upon which the table is fine and entry is evident.
I have tried resize, addrow to bottom of table variations and all result in the same core problem of a table corruption that crashes the file.
i have a range name (9 columns) and i filter for a certain type (Shrub) and i would like to only fill the combobox (on a form) with the filtered selection...?
I have recorded a macro which deletes the visible rows after an auto filter is run - ( auto filter is for custom and blanks).
I thought this was working, but when i look at the code, it looks as if the rows to delete have been hard coded in, and not adapting to just delete the filtered ones.
I am working on solving a problem where I get every other row deleted instead of every row. The situation is that I am using Autofilter to find empty cells in a column that shouldn't have empty cells. Before I can delete them, I need to add them to an array for exceptions handling. This means I need to step through the filtered range and record each unique ID for followup, then delete them. If I delete as I go, the "For Each" doesn't work on the original rows, but works on an increment. This means a deleted row causes the range to shift up one, and I end up deleting every other row.
I know I can solve this by deleting separately from recording them, but am searching for a way to work with the "For Each" maybe using a "Step -1" or something similar.
I am able to autofilter a sheet to show only rows with a date in Column U but no "x" in Column BC. Now I want to be able to put an "x" in BC in only the filtered rows. The purpose is if someone enters a date in Column U, the macro will show only those rows minus the rows that have already had an "x" added to BC.
For the most part I've gotten this to work using but it will also put an "x" in Column BC, row 3001 on down.
How can I get it to stop at the last row?
Cells.AutoFilter Field:=21, Criteria1:="<>" Cells.AutoFilter Field:=55, Criteria1:="=" Columns(55).SpecialCells(xlCellTypeVisible).Value = "X"
When I add an auto-filter, the above still works fine.
When the auto-filter doesn't reduce the no. rows, the above returns a value of 1.
I've tried using...
count = Rows.SpecialCells(xlCellTypeVisible).Count ... with a result of 1 if no rows are filtered
So, what is the best way to count the populated number of rows on a worksheet where there is an auto-filter that may, or may not, reduce the number of rows ?
I have discovered that my blue highlighted code posted below isn't deleting the visible rows of filtered data as expected.
Code: With core_data .Activate .Range("D5").Select Dim rngToDelete2 As Range llastrow = 0 lmergedelete = 0 llastrow = .Range("a65536").End(xlUp).Row 'calculate the last row visible 'filter the visible rows to include only those rows in which dupphase3 = "DUP" (ie. duplicated rows)
[Code] ....
Additionally, and I'm not sure if it's related to this, but I am receiving an error with the red line .... "Application-defined or object defined error".
What is required to delete the visible rows post filtering?
ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=9, Criteria1:= _ "Other" 'insert code to delete visible rows ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Select ActiveSheet.AutoFilterMode = False Selection.Delete Shift:=xlUp 'this is where the error is occuring. End Sub
I have filtered data on Sheet1, which I need just columns A, B & C combined and placed on Sheet2. The below code works, but its defined to copy all rows. I'm unsure of what syntax to use for the loop to copy just the filtered data. Also is there a way to "cycle" through the filters? Example Autofilter "1984" copy ABC to Sheet 2, then AutoFilter "1985" copy to sheet 2 and so on?
For Each Cell In Sheets("sheet1").Range("A:A") If Cell.Value <> Empty Then i = i + 1 b = Cell.Offset(0, 0).Value & ", " & Cell.Offset(0, 1).Value & " " & Cell.Offset(0, 2).Value Sheets("sheet2").Range("A1").Offset(i - 1, 0) = b End If Next Cell
From a combobox selection i filter a table for all entries containing the selected ID (from the combobox).
I first wanted to use selected columns from the resultant display (the filtered table) to populate another combobox so the user could drill down to the final selection that way, but seeing the mess i was getting involved in (I couldn't assign a range to the listfillrange of the other combobox) i think it might be best to settle for simply copying the visible cells to a new table on the selection page (the full database is on one sheet separate from the selection comboboxes and related controls), where the user can simply see the information needed on whatever line item they want - the number of filtered entries rarely exceeds five. What i can't understand is when i query the number of rows in the immediate window from the code snip below, it always comes back as "1", whether i do so on the full range or special visible cells.
[Code] .......
To load another combobox i tired
[Code] ...........
I really would like to be able to do so for further refining, if not feasible, can work with just a display table.
Querying the reultant rows i simply tried:
[Code] ...........
and
[Code] ....
In both cases, though the filtered table had 5 records displayed, the count was... 1.
1) Can the visible cells resultant table be fed into a combobox relatively easily and if so, how? 2) With the count of rows, what am i doing wrong?
I want to filter my dataset, select column B & C to copy, but exclude the header row. I am having a hard time offsetting the selected range. I get a compile error on what I try.Here is my code (yes, I know, its a little juvenile and I will clean it up, but I need to see things clearer at first):
My code below is supposed to move filtered rows in 2 sheets ("BANK ENTRIES" and "GL ENTRIES")with "Y"/"y" character on column J which is manually encoded by user. The rule is, before they can move, the total amount in column I in both sheets should match. That is the reason why rows in both sheets should be moved at the same time. I was able to figure out this rule with the use of an If Statement and a message box.
My problem is, if I run the code where there is only 1 row remaining in both sheets, "BANK ENTRIES" would insert a blank row in the third line despite my if statement
[Code] .....
The second problem is, If I run the code on "BANK ENTRIES", my first header on the "GL ENTRIES" will be deleted. If I run also the code on "GL ENTRIES", my first header on "BANK ENTRIES" sheet will be deleted. There must be something wrong on my code. I am attaching my working file for you to have a clear visibility on my problem.
I want to filter and then hide the filtered output in a range of data. But after I hiding, when I remove the filter, everything is there again. I don't know how to hide it even filter is removed.
I have some checkboxes that filter rows and a text box that should display the number of filtered rows:
If checkbox1 Then Range("F2").AutoFilter Field:=6, Criteria:="<>0", Operator:=And TextBox1.Text = "No of Records: " & Selection.SpecialCells(xlCellTypeVisible).Count
The text box is showing 1669380 record when there is only about 250.
what i need to do is store the number of rows visible on the data sheet (after a data filter is applied) into a variable so i can do a calculation with said data. Is there such a command?
for example....
data:
row header a | row header b line 1 line 2 line 3
after auto filter:
row header a | row header b line 2
code stores number of rows visible into a variable....
vVariable = rows visible... in this case 2 (NOT 4... which is the total rows including hidden)
I have a range that has been formatted as a table. Once I've applied a filter, if I try to select all of the rows, and delete the visible rows the option is greyed out.
Is there no way, short of vba, that I can just simply delete the visible rows?
I'm trying to calculate the number of rows in a matrix that have a complete row of data. I would noprmally do this using nested if formuals and a count function, but the columns of the matrix are filterable so the count won't work.
Is there a combination of functions that will adapt as I filter data?
