Determine Used Column And End Of Row
Sep 14, 2012
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")
View 4 Replies
ADVERTISEMENT
May 15, 2009
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.
View 7 Replies
View Related
Jan 30, 2014
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:
View 4 Replies
View Related
Apr 21, 2009
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 Related
Jul 3, 2014
I 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?
View 1 Replies
View Related
Dec 28, 2008
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.
View 4 Replies
View Related
Aug 29, 2012
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 Related
Oct 7, 2006
I 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!!
View 4 Replies
View Related
Nov 12, 2013
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.
View 9 Replies
View Related
Jun 14, 2013
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] .......
View 5 Replies
View Related
Feb 18, 2008
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?
View 3 Replies
View Related
Feb 22, 2008
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?
View 4 Replies
View Related
Mar 29, 2008
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)
View 2 Replies
View Related
Jan 22, 2010
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"........
View 4 Replies
View Related
Dec 23, 2008
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:
View 7 Replies
View Related
Jan 28, 2009
I am using the following functions to determine the highest row and column that contain data:
View 4 Replies
View Related
Oct 12, 2012
I 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.
View 9 Replies
View Related
Jan 17, 2008
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 Related
Apr 2, 2008
In 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 !?
View 9 Replies
View Related
Jun 29, 2012
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.
View 9 Replies
View Related
May 3, 2008
I would like to determine the values of cells in column B by the values in column A. I can do that for one cell but not for two or more.
Here is what works:
View 11 Replies
View Related
Dec 12, 2007
i need to have this information entered in the row below each time which may or maynot be empty. i need a message box with error handling to ask if the user wants to enter more products or finish and another message box if the entries (which start at cell B4) go past B10 that they have to exit and start a new sheet. I have put the code below and attached the workbook so it may be of use to others
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String
With Data
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
If rngFind.Row > 1 Then
ListBox1.AddItem rngFind.Value
End If....................
View 5 Replies
View Related
Jan 14, 2014
I have a big serie of charts (parabolas - open down) that i need to find the max value.
View 8 Replies
View Related
Jan 6, 2014
I would like to choose an option (C, Q, T) and get a result in 1 column.
In other words, if I type C in H6, I must get a different result in I6 as opposed to if I choose Q or T in the same column. However if I leave it blank I should have nothing or a 0 in I6.
In the attachment above you will see that I gave a sum for C (A3), Q (A4) and T (A5). The sum for each one is broken down into a percentage rate and a flat rate.
These rates are dependent on the initial amount entered into G6. C, Q, T stand for Cash, Cheques, Transfers. The form of transaction determines the % and flate rate to be charged.
View 11 Replies
View Related
Mar 21, 2006
I have a spreadsheet with Ethnicity (White, African American, hispanic,
Asian, other) in column A and Time (hh:mm:ss) in column B.
I trying to determine by how many White, for example, have spent less than 5
hours, >5 hrs but less than 10 hr, etc.
My plan is to make a chart of the data.
I've been looking at sumproduct and COUNTIF but I can't quite figure it out.
View 10 Replies
View Related
Mar 26, 2009
I'm working with box quantities and would like to get a formula that would take the box quantity and return the largest value based on some parameters. Here are my parameters:
Box Quantity
- 375 pieces
Break down options
2 - 187.5 pieces
3 - 125 pieces
4 - 93.75 pieces
5 - 75 pieces
Obviously options 2 and 4 are no good as I can only work with even break downs. This leaves me with either 3 or 4 and because I want the largest, I would choose number 3. What I need is help on a formula that will take the value of the box quantity and return the largest whole number. I think for the time being I would like to leave the break down between 2 and 5 as well.
View 5 Replies
View Related
Dec 13, 2011
What do I do if I want to determine whether an input is within a specified range, and then for there to be an output of a certain value from another cell?
For example, with tax brackets: if income (D) falls within B and C, return value A(from a separate column)?
Here is the data I have:
Column A
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
[code]....
Column D, cell D1 is where you would input a number (income) Column E, cell E1 is where I want the output value.
Basically, what I would like is a formula to put into column E that will do two things. First, to determine if the value in D1 is within either (B1-C1, B2-C2, B3-C3, B4-C4, or B5-C5). Second, to then output the value of either A1, A2, A3, A4, or A5, for each respective range.
View 9 Replies
View Related
Feb 2, 2012
I want to write a function that goes out and reads a cell, waits a few seconds, then reads the cell again, since it is constantly changing. Then report back the difference between the two readings of the same cell. The following code does not work since t0 and t1 always come back the same value.
Code:
Function ReadTime(t)
t0 = t
DoEvents
Application.wait Now() + TimeValue("00:00:15")
t1 = t
ReadTime = t1 - t0
End Function
View 3 Replies
View Related
May 16, 2007
formula I can use to determine age between date of birth (in one cell) and an event date (in another cell)., i.e. birthdate 3/15/2000 with an event date of 3/2/2007.
View 9 Replies
View Related
Apr 5, 2009
I am having a bit of amnesia right now, but I know there are some smart cookies out there who can do this in a second or two.
I have a certain range in a worksheet (D6:D27). I need to find the first blank row, or the last row with data +1. I would like a worksheet function of some sort that might be used like this
=LastRow(D6:D27)
The result would be row 10 (the first blank row).
I have been searching for the past hour and just can not get anything to work right. I have tried many different suggestions, but I still draw a blank.
View 9 Replies
View Related
