Unhiding Part Of A Range
Nov 18, 2008I'm trying to write VBA that will unhide all the rows in a designated range ("Apples"), except the first and the last.
I'm using this simple code to hide the range: ...
ADVERTISEMENT
"Unable To Set The Hidden Property Of The Range Class" When Unhiding Cells
Oct 30, 2009I'm trying to unhide all the cells in a worksheet, but keep getting the "Unable to set the Hidden property of the Range class" error and I'm not sure what to do.
View 2 Replies View RelatedSum Range With Part Text In Another Range
Dec 13, 2006I have to group some data from a pdf format.
I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...).
Is Named Range Part Of Another Range
Jun 29, 2007I am trying to check whether a cell is part of a named range. I have a 4*2 range named "kompleks" and wants to check whether the selected cell i part of that named range. I've searched google and this forum, but nothing will work. I've tried
If Target.Name = Range("kompleks") Then
If Target.Name = kompleks Then
And also
If Target.Name.Name = Range("kompleks") Then
It is used as a private sub for the worksheet_change.
Sumproduct Only Part Of Given Range?
Jan 23, 2013I have below four column range. I need a formula to sumproduct column A and column D, where column B = "n1", column C = "xyz" and until sum of column A reaches first largest value which is less or equal to a variable, say 15. So, the rows would be 1st, 2nd and 5th. And the result - 1,440.
A B C D
2n1xyz110
5n1xyz112
8n2abc112
3n1abc111
6n1xyz110
6n1abc114
3n1xyz114
2n1abc112
3n2xyz114
8n1xyz114
8n1xyz115
4n1abc115
I have worked our an CSE formula below, but it is really massive. Need to have much simplier one.
={SUMPRODUCT(--($C$1:INDEX($C$1:$C$12,MATCH(LARGE(IF(($C$1:$C$12="xyz")*($B$1:$B$12="n1"),$A$1:$A$12),COUNTIFS($C$1:$C$12,"xyz",$B$1:$B$12,"=n1")-SUM(IF(FREQUENCY(IF(MMULT(--(ROW($A$1:$A$12)>=TRANSPOSE(ROW($A$1:$A$12))),--IF(($C$1:$C$12="xyz")*
[Code]....
Add Decimal Part In A Range
Mar 9, 2008How can I add decimal part of values in a range
eg: assume my range has 2.27, 3.1, 3.725, 4.1, 4.35
result should be 789 (27+1+725+1+35)
Use Part Value Of A Range In Userform Refedit
Feb 27, 2013I have a refedit box in a userform wherin i will be selecting a range and thus getting a value say "$A$1:$A$2"
Now what i need is that it should seperate the value as follows so that they can be used in another place
starting column to a variable x.
starting row to a variable y
similarly
ending column to a variable p
ending row to variable q
Looking for code that if i select more than 1 column in the refedit it should tell me to select a single column and not more.
Picture As Part Of A Named Range?
Mar 4, 2013I am looking to make pictures a named range to be used in a drop down box. I insert the pictures on sheet 2 and name the cell range they are in, but the drop down box on sheet 1 is blank??????
View 4 Replies View RelatedFind Part Of Work In Range?
May 5, 2014I want to go ("E:E") and if part of the cell contains "WAL-MART" than put the word "Food" in cell G of the same row and contunie until end
View 1 Replies View RelatedSelecting Only A Part Of A Merged Range
Aug 1, 2008i have a merged cell which runs across a multiple columns (but a single row). i would like to insert a column in the middle of this merged cell, but whenever i click on the column header to insert a column, the entire merged range is selected.
is there a way to change the excel setting to avoid this?
Search Range For Part Strings
Aug 21, 2008I am using the following code to search a database of information and then display it on a different sheet.
The user types the search term into a textbox and then presses a command button to search the database.
Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?
Sub SearchDatabase()
Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range
On Error Resume Next
Replace Part Of Text In Range
Aug 25, 2006I would like to use some vba code to search range T3 to U500
and search for the word "all out" which will appear within the text of some cells - in the format :-
64 all out(36 overs)
and replace it to
64 all out(50 overs)
the two digit total at the beginning of the line can be 1-3 digits and the number of overs can only be 1 or 2.
Search For Part Of String In Range Of Cells
Jan 17, 2012Following problem:
I have a list of changes on our bankaccount.
Sometimes, in the description a payment we receive the name of the person who transferred the amount is mentionned.
It can be anywhere in the string, it can be just his/her first name, it can be his/her full name...
I also have a list of all our clients with first name in column A, second name in column B
Something like this:
string from bancaccount:
EUROPESE OVERSCHRIJVING VAN BE16 6712 5615 7974 BANKIER OPDRACHTGEVER: EURBBE99 VANDERSMISSEN WILLY VAN PLAKSTRAAT 176 9000 GENT 68/10762827 PENSIOEN REFERENTIE: OV-0000236-00240
client list
Francois D'hondt
Germaine Canipel
Willy Vandersmissen
Karel D'hondt
.
.
.
I'd like VBA to search in the clientlist for names that occur in the bankaccount string.
Sice ther might be a lot of Willy's in the client lsit I think it will be best to serch for the combination of both first and second name.
Once found the name the procedure should return the rownumer on which the client can be found in the client list.
Sum Part Of Named Range And Pivot Data
Mar 14, 2012I have a named range TOT_BUDGET. We use the current month number to access this range's index for current budgets.
=INDEX(TOT_BUDGET,3)
My quandary is that I need to calculate the YTD budget for one of our dashboards. So, I need to get months 1, 2, and 3 and SUM them. Next month I'll need to get 4 months, etc...
I've looked at SUMIF, but it needs to match against some sort of criteria... I don't have any numeric month labels on the budgets page. I cannot figure out what range/array I would match the month number to.
Now, I know the easy way would be to put a month number at the head of each column, reference that array and do the SUMIF. Blech.
Ideally, I would love to just be able to say =SUM(INDEX(TOT_BUDGET, 1-3)). Is there an Excel function that will let me select an array slice?
------------------------------
In addition, we are getting the Total Sales number out of Great Plains via an SQL query. We summarize many accounts using a pivot table. We then access this table by using the GETPIVOTDATA function.
GETPIVOTDATA("NETCHANGE",TSALES,"FISCALYEAR",CurrYear,"FISCALPERIOD",CurrMo)
Again, how can I do a total YTD?
Is there a way I can do something similar to a foreach loop in the cell formula? I'd like to avoid macroland with this if possible.
Find Part ComboBox Choice In Range
Oct 28, 2006I have ComboBox on a UserForm that is looking to a long list on a worksheet. A lot of the entries in this list start with a brand name instead of a more discriptive name. I really need to be able to find an entry with any key word (not just the first word) in this list.
View 7 Replies View RelatedMultiple Lookup: List Of Part Numbers Based On The Product Part Code
Feb 12, 2009I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
VBA Formula; Sum Up Part Of A Column Based On A Date Range
Feb 24, 2009I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).
View 3 Replies View RelatedCount Instances Of Part Cell Text In Range
Jan 21, 2008I need a formula which I am using to count instance of sickness. I include a sample copy of my data which I hope will make my request clearer! I'm currently using the below formula to count the number of continuous instances of any code starting with "SICK" =(SUMPRODUCT((LEFT(C$10:C$37,4)="SICK")*(LEFT(C$10:C$37,4)<>(LEFT(C$10:C$37,4)))))
I would also like this formula to count any code starting with LTS as well (Long Term Sick). Due to the nature of sickness there may be continuous periods where both SICK and LTS codes are used, at the moment I can not come up with a formula to return the value I need. These columns may contain other codes but for simplicities sake I have only shown the relavent ones.
Hiding/Unhiding Rows
Feb 8, 2010I have a problem with hiding and unhiding columns. Is there a way wherein I can automatically hide the row(s) if the cell = "N/A" and will unhide if it's not equal to "NA". see the attachment Im using excel 2003 btw. You can see in Sheet2 to that it contains the table for the student grades. "N/A" means that they are not enrolled on that subject. While in sheet1, What I want to happen is that you will select a student name and below, it will automatically display the grades of the student on their enrolled subjects only. So, those fields with "N/A" will hide and when I update the value of N/A it will unhide.
View 5 Replies View RelatedCan Workbook Be Activated Without Unhiding It
May 8, 2013I have a macro that activates and copies data from a hidden workbook. The problem I'm having is that when the macro is done, the workbook is no longer hidden.
I used this code here to "re-hide" the workbook...
Code:
Windows("Macros.xlsm").Visible = False
...but now, everytime, it asks me if I want to save the changes to my workbook. I would like to copy a worksheet from this hidden workbook without having to worry about whether to save changes or not everytime I run this macro.
Hiding / Unhiding Columns
May 20, 2007I have a spreadsheet that i would like to hide all columns from B:M and to select the column/s I want to unhide.
I would like VBA cose to do this or to set up a combo box that will allow me to hide/unhide specific columns....
Unhiding Hidden Tabs
Jun 6, 2007I am very new to this forum so this probably has been covered (although I searched but didn't see it.
Anyway, I am trying to figure out how to unhide multiple tabs instead of one at a time.
VBA For Hiding/unhiding Rows
Sep 27, 2007For data validation lists B27 to B30, if any of them is equal to "EBS via ULL" then unhide rows 31 to 42.
If any of the data validation list not equal to "EBS via ULL" then unhide row 44 and hide rows 31 to 42.
If "EBS via ULL" and any other product in the lists is selected, unhide rows 31 to 44.
If validation lists B27 to B30 are all equal to "Select Product..", then hide rows 31 to 44.
I would like to use the worksheets_change event.
Unhiding Personal Workbook
Jun 12, 2009I search the web and found a few different instructions, however the all lead me to the same point. My unhide button is greyed out and not an option. The workbook is in the correct location on my machine, etc. For some reason I cannot unhide it and have it show in my VBA window...
View 9 Replies View RelatedReturn TRUE If Part Text Found In Cell Range
Feb 27, 2012The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.
Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:
A B C D Results:-
1 class1 class2 class3 class4 Gg Hi Fr Sp
2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE
3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE
Unhiding Text Based On Result?
May 8, 2014see the sheet attached. To put into context, I am a teacher and want students to work on tasks, and have the answers to those tasks available to them but hidden, until a give them a password that unhides it.
On the attached document, Lesson1! K35:Q41 i have some answers to the task beside it in B35:H40. I want to hide these answers in some way (without affecting the columns as there is data above) and then when I want students to see the answer I give it to them and they enter it into L43 in this example. At this point the answers are revealed and stay revealed for revision purposes.
This will be repeated using different passwords for different tasks throughout the workbook e.g. L52 will be a different password revealing the answer in K48.
Unhiding Columns (column A That You Can't Click On!)
Jan 29, 2009I have columns A to N hidden and need to reveal them. I know you can manually drag each one to reveal it. But I thought there was a way through highlighting/menu options to reveal them all instantly.
View 3 Replies View RelatedHiding And Unhiding Rows With Checkbox
Jun 17, 2014I am trying to Hide and Unhide several rows depending on if several checkboxes are marked or not.
[Code] .....
This is the code I am using, which is effective in hiding and unhiding the rows. However, the issue I am having is: I want Row 20 to stay visible if either one of the checkboxes is marked. This string of code runs the "hide" portion if either box is unchecked.
Hiding/unhiding Specific Rows Containing Zero
Jul 23, 2008I would need two macros for one of my projects. I did search the forum, but I couldn't find anything what would suit my needs.
The first macro should hide those rows which would contain 0 (zero) in a specific column (in my workbook it's E). The secon macro should unhide the hidden rows.
Sound so easy ... but sadly I can't do it.
Hiding And Unhiding Two Sets Of Worksheets
Oct 11, 2008My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.
I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.
I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.
If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows
