I have a workbook that has 2 sheets; "Quote Summary" and "ECS". The "ECS" sheet contains columns "C" through "L" each of which has a total on row "27". At any time, one or several (but never All) of these columns may be unused and therefore hidden.
I would like to know if it is possible to sum the total of only those columns that are NOT hidden and display that value in cel "C15" of the "Quote_Summary" sheet.
I am calculating the average for a range of cells in a row. =AVERAGE(D7:P7). I need to hide some of the columns and calculate the average based only on the visible columns. How can I do that?
View 5 Replies View RelatedUsing VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
(Excel 2003 on windows XP.)
I need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W".
I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's.
OK, I have a spreadsheet that has several pages for tracking when items are added and removed. On the main sheet I need to have the total items added, the total items subtracted, and the total avaialble. This is also based off of month.
For example, on sheet 2 it would show 5 of an item added, 2 of an item subtracted, all for March. It would also show 3 items added, 2 subtracted for April. On Sheet one i need to report in a row any items added, any subtracted, and the current total. I am pretty sure that i have the formula for added items and subtracted items for the given month, but i do not know how to get the current total.
Note that the "Fruit" and "Fruit Totals" column letter position will always be different every time I run the macro, so I would like the macro to find these columns by name rather than by column letter.
I'd like a macro that adds the total number of fruits in the "Fruit" column by looking doing 2 things...
1. Add the total number of "Fruits" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Fruits Total"
2. In the cell directly to the right of "Fruits Total" put the value of the total number of fruits in this cell (which will be in the "Fruit Totals" column)
3. Add the total number of "Apples" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Apples Total"
4. In the cell directly to the right of "Apples Total" put the value of the total number of apples in this cell (which will also be in the "Fruit Totals" column)
5. Repeat steps 3 & 4 for the other fruits
Best to show you an example ...
My data in excel looks like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5
What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7
I have a worksheet that has rows of information up to row 3000. Sometimes all the rows are shown and sometimes a lot of them are hidden. When I try and print, the rows with info are printed but then the hidden rows are included as just blank pages. Is there a way I can get excel to print only the rows in 1-3000 that are shown and not hidden?
View 1 Replies View RelatedIs it possible to have a macro which navigates to next unhidden worksheet? I am currently using the macro noted below, but they pick up hidden worksheets. Next sheet:
View 3 Replies View RelatedI have a table, that while writing a vba code I need to select the first column in the table (A) and the last column with data inj the table ( unknown which column it is). The last column might change (meaning that it sometimes column B, somestimes column Y etc...depends on the data). I need a way to select only the the first AND the last column in that table(actually it's the last column with data on that sheet). I'm sure there is must be a way I just don't know it!
View 2 Replies View RelatedThe small attached "Demo2" file indicates my problem. In the "Scorecard" sheet I'm using macros driven from listbox menus to hide or unhide rows on the "Tasks" sheet. It is VITAL that the rows on the "Tasks" sheet not be deleted or have new rows inserted. But, if I Protect them then the list box macros won't function. Is there a macro that will allow these macros to hide or unhide rows without allowing a user to add or delete these rows in the "Tasks" sheet? If possible a simple "global" solution is preferred as I have 100+ If/Then entries to contend with...
Attached File : DEMO2.xlsm
I have a file that contains multiple rows of data. I built a macro that will then hide rows with a $0 balance. When the macro is completed, my spreadsheet is only showing the lines with a $ amount. (The $0 rows are hidden)
What I'd like to do is then format the unhidden rows so that each alternate row is color coded. This will separate the rows visually so I can more easily follow the rows across to view $'s per line.
I've attached a sample spreadsheet (it does contain the macro to hide the $0 rows). How can I then format the remaining rows with alternate shading?
I dont know if a vb code is at all possible to copy a formula in a cell in row 2 straight down that column until the last unhidden row in which there is at least a cell populated with values from the 256 cells in that last unhidden row. Then in that column, I want the cells that are filled with formulas to be converted to values.
All these unhidden rows are autofiltered with certain criteria. The left and right columns to the formulated cells does not necessarily filled with values as they can be blank. Row 1 has header names, and a formula can be any cell in row 2 at any one time and that is exactly the problem i faced so that to record a macro seems totally useless for me because the cell in row2 where I will begin with a formula varies every time.
Currently I go a long way by taking these steps:
Hghtlighting row1
Data/Filter/ Autofilter/
Select the header cell and click on drop-down filter
Select (Custom...) with criteria
Input formula on the cell in row2
Copy the formula
Press Shift+ArrowDown keys until the last unhidden row
Press Enter
Undo Autofilter
Select the column
Copy and Paste as values
Autofilter row1 again
Select the header cell, (Custom..) equal "#N/A" and "zero"
Continue next turn of formula..copy to last unhidden row, paste as values....
Me and other staff in my department are doing these long-winding steps endless of times, day in day out, until we are drained out and called quit. If there is a code that can do this, this is going to the greatest news ever for these people for that kind of work.
I have a worksheet that currently has columns AB to XFB hidden. I want to unhide them, so I selected column AA and then drug the mouse over to the right (toward the empty space), intending to right-click and select "unhide". I do this all the time with no problems.
However, from the instant of my drag to the right, Excel has become totally unresponsive. I see it using 25% of my quad CPUs in task manager, but there is no active memory delta and it has been sitting for over 20 minutes now.
I'd rather not lose the work I just entered, but I can't seem to get Excel to come back to life by pressing escape or clicking in the worksheet. I've noticed slowdowns before when there is a lot of actual data in the cells selected, but these columns were empty to the best of my knowledge.
I managed to make hidden sheets unhidden. However, after doing so, the first sheet stays active. How can I correct this? EXCEL 2010.
Module4:
Code:
Function Hide_Show(MySheets)
ActiveSheet.Unprotect Password:="****"
Application.ScreenUpdating = False
For Each ws In Sheets
X = Application.Match(ws.Name, MySheets, 0)
If Not IsError(X) Then
[Code] .....
Sheet WORKSPACE:
Code:
[Private Sub Open_BusinessDone_Click()
MySheets = Array("WORKSPACE", "BUSINESS_DONE")
Call Hide_Show(MySheets)
End Sub
I am looking for a way to select specific columns in excel that have specific characteristics in the header titles. For example, I have a very large worksheet with columns that specify the year and month (ie. "1900-8"). The years range from 1900 to 2011 and each year has "-1" to "-12" to represent the months of the year. Specifically, I am wanting to pick out all of the columns ending in "-8" (August).
View 14 Replies View Relatedin a vba module I currently have this
View 2 Replies View Relatedi have the following table, i want my Vlookup not only get the description from Column K but to get column K , L , M City and Country as well and put them in Columns D for Description, E for City and F for Country. would this be possible with Vlookup formula or any other formula?
View 3 Replies View RelatedI wish to select all the cells which contain the word " Available " in column A at once . But i didn't find any option to do so using excel's Go TO Special feature.
I am using Excel 2010.
I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.
This is the code I have right now, which works beautifully for the whole table:
Code:
ActiveSheet.ListObjects("Table36").Range.AutoFilter Field:=6, Criteria1:= _
"=Alan", Operator:=xlOr, Criteria2:="="
Range(ActiveSheet.ListObjects(1)).Select
Selection.Copy
What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?
Using Excel 2010.
I have an employee schedule that is a whole year. I'm trying to extract only the next two weeks worth of columns. I'll schedule it to run once a day. Some information... The calender has the dates listed in Row 2 on Sheet called Daily Schedule The 2 week calendar would be on sheet 7 called Two Week Calendar There are only weekdays on sheet 1 Daily Schedule so if there is nothing matching today's date just stop and do nothing I would like it to save the whole file when complete
I have written some of the simplest code but I don't know how to select by today's date then copy that column plus 9 to the right.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:Schedule.xlsx")
[Code]....
I have a worksheet which is used by multiple users at the same time, at the end of the day I can have upto 10 files. The layout of all the files is the same, same number of columns and rows, the only difference would be the file name eg:Test1.xls, Test2.xls etc. information in test1.xls will not be any other file. At the end of the day, these files are emailed and saved in a particular folder.
What I need is get information from all the files saved in a particular folder and keep columns which have data. Additionally, if there is no data for any columns after combining the columns to display an alert. The final file should have information with only data in individual columns and arranged in the order such as Center 1, center 2, Center 3
Eg:test1
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 center 3....upto 30 centers
1 4
2
3 5
test2.xls
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 Center 3.....upto 30 centers
5
The final file will look like this
Finaltest.xls
Column A Column B Column C.....upto 30 columns
1 4 4
2 5
3 5
I have quite a long sequence of column for which the headers are all two-lines titles (with a line break). I need to copy many columns that are discontinuous, and the operation would save me a lot of time if I could select them at once. Of course, I know about selecting the columns holding CTRL, but here is the snag: as soon as a two-line title column is selected, the formula bar displays the content of the header, obviously on two lines, and I can't select any more columns. I tried many things, but nothing could solve this...
Is there anything I can do besides copying the columns one by one (which would take a long time) or renaming them?
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
See the attached image to make it clearer..
Excel2010
Excelhelp.jpg
I want to fill an array from values in range A1:H10. I want to fill the array with all rows in range and only columns B,C and E. I have the code below so far using index function.
Is there a more direct way to select all rows from desired range to avoid the need to create an array of rows from 1 to LastRow and then use Application.Transpose(RowsArr) (in red) inside Index()?
I would like to loop through cells row wise.
Columns which have data are A:E
modify below code to select the range from columns A to E?
[Code] ........
I have Excel Sheet that open with user name and password.
Like if it's open my Admin - he can view all Sheets
If open by any user - it opens only users sheet.
Problem is once file is saved by any users, all hidden files get unhidden and Admin Sheet is open.
Requirement: if User1 saves file other sheets should not get unhidden.
I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.
Pseudo code (how I'd like the syntax to be, but isn't):
Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With
I have Columns A to C which are hidden. I have tried to unhide these bey selecting the entire worksheet but to no avail. I am using Office 2010.
View 4 Replies View Relatedim looking for a basic macro for workbook copying, all workbook(all hidden, unhidden, and very hidden) sheets.
i know how to do unhidden sheets of course however the hidden and very hidden are giving me some trouble...i need to also copy over all the macro's, buttons, and drop down box's as well..not just values.