I would like to determine using VBA which Column or Range-of-Columns have a filter applied.
I need to change filters, depending what part of the sheet the user is looking at.
Not sue why, but in my IE browser, I cannot Paste code anymore .
Code:
Is there a way to automatically determine the final row number showing after a manual filter operation? Or maybe getting a list of the row numbers showing on the screen.
The project requires a macro that loops through only the rows of a filtered list, not all of the rows in the worksheet.
Right now in my code I'm using the below section to determine the column in which to search for dupes, and the last column of data for that range. However, I make the user select only part or one column through IF statements above, so there's no need for the user to input this information. I'd like to replace the choice part with and automated routine that determines the DupeCol from the column section selected, and determines the rowend from the last used cell to the right in the rows in that column selection. But I don't know how to do that and keep the values as strings.
VB:
Dim RowEnd As String, DupeCol As String
Dim LastRow As Long
DupeCol = InputBox("Type in the letter of the column you want to look for duplicates in.", "Fill in the Info")
RowEnd = InputBox("Type in the letter of the last column you want colored.", "Fill in the Info")
Trying to do a simple workbook here, with hopefully no macros/VB script.
Essentially, I want a cell to display the row number (-1 to take into account the heading) which has the last value contained in it.
The workbook is a list of users that have signed up in 1 month, and is distributed to staff for a particular reason.
The cell I'm trying to create is part of a 'stats' section.
Is there any way to determine whether a column is sorted? Like a property of a column like ".ascending" true or false?
View 9 Replies View RelatedI have a table with this sample data:
A Report Number
B Funding Code
C Approver Code
[Code]....
For cells with column F="NEXT" and matching values in column A (i.e. Rows A2-A5 all match) and "Yes" in any of those same rows (i.e. E2-E5) for column E, column G should read "No". If no "YES"s are in those rows (i.e. E2-E5) then column G should read "Yes".
I can't figure out using VLOOKUP or INDEX and MATCH how to make this work. Would I need to get into VB to do this?
I have a situation where i have headers in a file but just because there is a header does not mean there is any corresponding data in that col.
I was keying off if just the header was present then i need to do "something" but now i need to key off two things:
1) Header present
2) If there is anydata in the col when a header is present.
Question: How to use vba to determine (quickly) if there is any data in a given column.
I am looping through columns in a sheet. When I reach a column is there any way of determining if the column has dropdown list of values? I was expecting something like "HasDropDown" property somewhere, but it does not appear so. Some of my columns have dropdown values others don't and I need to determine which ones have.
View 2 Replies View RelatedI have the following code which will determine if a user has selected a entire column.
If Selection.Rows.Count = 65536 Then
MsgBox "User has selected an entire column."
End If
Although it worked, but is there a better way to do it? I was thinking if future version of Excel goes beyond 65536 rows, then this code is definitely unusable!!
I have a spreadhseet that has rows with headers, blanks & total lines throughout that need to be deleted. There are also about 48 rows of data at the bottom that are not needed.
The row numbers will be variable each time the report is run. I need the macro to find the last row of data in column A, use this as the last row in the range and delete all rows above this unless there is numbers 1 to 12 in column J.
I am trying to determine for each account number in column A, the most popular method for accessing our services based on visits in column B. And then having those numbers summarized as a total for each method.
See below for an example. As you can see Mobile was the most popular for 2 clients Java was for 1 and Desktop was not for anyone. Not all accounts will use all methods.
Account
Visits
Method
1
10
Mobile
[Code] .......
Im trying to write a formula that will check if a name that I have in column A also appears in column B and if it does I want it to return a value for example TRUE in column C.
I'm currently doing a survey and I have all employees in column A (600persons) and then column B I enter thos that have taken the survey. So If an employees name exist in column B he/she has done the survey and then they I get the value "TRUE" or "1" or something else. So in this way I can easily see who has and who hasn't done the survey. I've been trying with VLOOK and so on but I can not get it right? Maybe I need VBA?
I looked at a couple pages of Ozgrid solutions for people that were having word wrap issues and decided my issue wasnt related to any of them - my cells are not merged and they are not exceeding 1024 characters. I have a sales sheet with column headings as the name of the product, many of which have long names (but all less than 100 characters). For example, one cell value is "Flow Meters And Regulators 2007" and another is "Sphygmomanometers 2007".
My column width pre-format is 8.43 and after format is 7.86
The only code I use to adjust the row and column size is
With Rows("1:2")
.WrapText = True
.AutoFit
End With
Columns.AutoFit
As you can imagine "Sphygmomanometers" breaks into two lines in the short confines of 7.86 width. How can i get the column autofit to adjust to the full word?
I require a row of details to be copied to another worksheet by typing in a unique ID using a macro so Sheet 1 is a data base of items (every item have a unique code like 1001, 1002 etc) and sheet 2 has a table, and next to the table is a cell, which i need to work like a search engine.
i need to be able to type the unique id in a cell, in sheet 2, then click an Add command Button. This button then finds the unique id in sheet 1, and copies all the items details in the same row, into sheet 2 in the table, then i require the search engine to be cleared for the next item to be added. (Assumed Experience:Below Average, I know few formulas and know very basic macros)
I have 1500+customer names in column C7 of table. I'm looking for a SUMPRODUCT formula that achieves the following 2 logical goals. First, it needs to determine the number of unique names in column C7. It can't just count the rows because many customers appear multiple times in the range. So, I have been using the following formula and it works great:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&"")))
Second, I'd like the formula to tell me how many of the unique names in C7 correspond to public companies. Column C8 of my table indicates whether or not a company is public ("Yes") or private ("No"). So, I've been using the following formula and it seems to deliver the right result:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&""))
*
(COUNTIFS(Table[C7],Table[C7]&"",Table[C8],"Yes")>=1))
But the added COUNTIFS statement seems very unwieldy and indirect to me. Why do I have to examine every instance of a particular company's name when the answer could be determined by looking at any instance of the name -- after all, each company in C7 is either public or private, and has the same corresponding value in C8 (whether "Yes" or "No") everywhere that its name appears in C7!
So that's my first question: is there a clearer, more efficient way to achieve the same result? My second question has to do with toggling the public company test.
What if I create a special cell, say A1, that will always contain either the word "On" or "Off"........
I am trying to resize a picture on a worksheet to fit a dynamic measurement(the size of the page is different depending on the contents in cells).
I am doing this all in VBA.
so what I have come up with is that I need to
1) determine the total column width of only the non-hidden(visible) cells.
2) resize my picture to those measurements, so I can get an exact fit, and it doesn't create a new page break, by going over.
I have found some code on forums, and am thinking of something like this as a solution(although I don't know how to complete it):
For summing column widths:
I am using the following functions to determine the highest row and column that contain data:
View 4 Replies View RelatedI am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:
=IF(AND(A3="neuron",A3=A2,A3=A1),1,"")
And copy down. I then test column B to see if there are any non-blank cells.
If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.
I can do this with a UDF, but the user has rejected this option.
I have a database that holds thousands of names and the columns are of trinings that they have completed. I can filter by the criteria of name and of ID. ID number is unique so what I want is to know if Column F has a date in it once filtered. It maybe line 789 but that is all it shows because the filter is on.
View 2 Replies View RelatedIn the enclosed wb you will find 2 custom functions.
The IsRowHidden Checks weather row 3 is hidden and works fine.
However, IsColHidden does not change from False to True upon hiding col. C.
What did I do wrong !?
Is there a way to filter column A but ensure it includes multiple lines of data in a different column.
I have attached an example of what I am looking for:
I need to filter by community but keep all the information listed in columns, B, C, & D.
So when I filter to Thunder Bay I would still see the 5 members listed in column D.
I realize the easy solution is to fill all the empty cells in column A to pick up the data in the other columns, but I do not want to Fill all the empty cells to include the name "Thunder Bay" (not the look I want for this spreadsheet).
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
How to filter certain row when column contain certain value using vba? i try to manually add in filter using Excel, but it doesn't works, how to write vba code to filter out rows of which some of its columns contain certain value?
View 5 Replies View RelatedI have set of data(attached example file) in which i have to categorize the data depending on the LOC (column C) which is below 1000,between 1000-3000 and greater than 3000.
Once i have filtered data i need to get the counts for each impacts.
I have put the sample output in next tab(sheet 2)
I am trying to macro a filter for column C & D to hide rows that contain blank values in BOTH columns.
In other words..keep row if value in column C OR D OR BOTH.
Original Data Set;
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
ABCDE 1WH4_BL_STKTRUE 1 2JBS_BL_STKTRUE 1 3Total [Mill]TRUE10001,7001 4Total [Waste]TRUE50000 1 5Total [Stockpile]TRUE 1 6Total [Mill+Waste]TRUE 15,0001 7RPG4_CR_DST {WH4}TRUE 2 8CR_DST {WH4}TRUE 2 9JB_CR_DST {WH4}TRUE 2 10RPG4_CR_DST {WH123}TRUE 2
Desired Filtered Data;
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
ABCDE 3Total [Mill]TRUE10001,7001 4Total [Waste]TRUE50000 1 6Total [Mill+Waste]TRUE 15,0001
Filter Even The Coloumn No Changes
how to Filter Even When Coloumn No Changes
i have a macro to filter various coloumns on various criteria assuming the headings are same everyday.
But my parent co employee sometimes changes the heading order('exceed' heading changed like 'u' clumn to 'v')
how to cope with this in macro/VBA
I have a spreasheet with one locked collum that I don't want to be edited. Is there any way of filtering while this collum is locked? It seems to be that once it locks the collum it also locks the filters?
View 6 Replies View RelatedI am using the following code to filter one in one column.
View 8 Replies View RelatedI have been trying to filter a column from the value entered in to a cell and hide all rows not matching.
I would like to have a macro button that will look at cell C2 and then filter column E from cell E5 down to the end of the sheet since the row count will change often. I have attached the sheet I'm working with.
So I enter HCLU into C2 hit the filter button and all rows with HCLU in the E column stay visible and all not matching HCLU become hidden or deleted.
I've managed to use the following VBA to filter a sheet based on the value of cell B1:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Intersect(Target, Range("B1")) Is Nothing) _
Then
Exit Sub
End If
Cells.AutoFilter Field:=1, Criteria1:="=" & Range("B1")
Range("B1").Select
End Sub
I have tried to use this for a different sheet with "fixed head data" that needs to stay at the top of the page (range B1:K7), for printing purposes.
How to modify the above code to filter column A (from row 8 downwards) depending on what's entered in the cell A1.
Also - if possible, "if A1 is empty - display all rows".
