I have a list of names in A:A how can I hide the rows that will start with the name (John)
A B
1 Tina 12345
2 Tina 5468
3 John 5566
4 Peter 444885
5 Tina 44653
so that way all I see is
A
1 Tina
2 Tina
5 Tina
I want to create a macro that checks whether a specified range of columns is hidden, and
a) hide them if they are not hidden, or
b) unhide them if they are hidden,
i.e. toggle the Hide state.
What method can I use to query the hidden state of a selected range of columns and return a boolean value?
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
If A1 is also found below A1 (A2,A3,etc) then "HIDE" otherwise "DON'T HIDE"
I want to create a formula which finds out if a value exists below it in the same column. I could then use a filter to remove rows contain the word "HIDE" which will leave me with only rows with DON't HIDE. Then Column A will only contain unique values.How would I create a formula like this?
I have a table which holds scores as well as other data for players. Is it possible to automatically sort a table based on their points score to see who is top of the league?
View 9 Replies View RelatedI’m aware of the AutoRecovery, but I’m looking for an Autoback (Auto save) itself. Is there one in Excel? ( I'm using 2003)
View 2 Replies View RelatedI’m not even sure I can do this- but- I want to be able to have a few columns, 3 of them I want to be Item #, Item, Cost.
Is there a way to put the Item # (Number) in, then have the other 2 automatically come up?
I am inputing each day the total amount of sales. I am manually doing the averages for past 12 weeks on Monday sheet. I would like when I enter the amount for that week that the cells B84, C84, E84, F84 are automatically updated for the past 12 weeks. I have tried a few different approaches but I keep getting #ref or circular reference
View 10 Replies View RelatedI'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems...
1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately.
2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them?
3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?
supposing i have column A which is vehicle number
and column b is fuel quantity
how to make in c automatic sum by vehicle number as i may have many entried in column A
example
vehno1 20
vehno1 21
vehno2 25.5
vehno2 40
vehno3 50
A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".
View 7 Replies View Relatedformula for input values.I need to put in table_1 every to each fourth row each first row from table_2.
For example:
in table_1 A1 input A1 from table_2
in table_1 A5 input A2 from table_2
in table_1 A9 input A3 from table_2
in table_1 A13 input A4 from table_2
And so on.
I have data in table-1, in current shape
Table-1
B C D E F
Adda Zakeera 1239987801310037
Ahmed Pur East 5559998803310042
Ahmed Pur East-2 8888874805510041
Arifwala 3545555805510045
Bahawalnagar 3336666802610046
Bahawalnagar-2 1257777806610038
Bahawalpur 2206666804410044
Bhakkar 2223333805610042
I want to arrange my data like Table-2. I have tried vlookup() formula, it worked but every time I puldown the formula I have to manually increase cell # (e.g) In the row of Adda Zakeera, I have 4 values. When I use vlookup() and drag it downward the values should be shown as in Table-2 under Adda Zakeera.
=VLOOKUP($D$2, Sheet1!$B$4:$AF$90,2,0)
Table-2
D E F G H I J
Ahmed Pur EastAhmed Pur East-2 Adda ZakeeraBahawalnagarBahawalnagar-2BahawalpurBahawalpur-2
555 888 123 333 12548 2201#N/A
555 888 9987 333 77777 66666#N/A
I'm working on Excel sheet which cover period of time, I specify the period on the top, (cell E4= FROM : cell G4= TO) then I have to fill column (B) with the days name, and fill column (C) with the starting date from beginning till the end date.
i.e.:
E4= 24/06/2014 G4= 23/07/2014
B9= Tuesday C9= 24/06/2014
B10= Wednesday C10= 25/06/2014
I'm getting this work by a VBA code, and if it's possible to have the days names in Arabic language, and set the print area from (A1) till the (next) row after the last day in the column i.e cell (G39)
how to print automatically from Excel to a PDF file. After a couple of days of searching and tweaking, I've got what I think is a fairly good solution for automating your printing of worksheets to PDF files.
This solution requires the use of PDF995. This is a free utility available at www.PDF995.com. This is a print driver that allows you to print to a PDF file. The file is compatible with Adobe and can be read with the Adobe reader like any other PDF. The free version does pop-up some advertising with each print, but the automation works with around the pop-ups. I believe the full license version is about $10 and doesn't produce the pop-ups.
The challenge in automating a PDF process is that the PDF driver will prompt the user for a filename. This is ok if you are printing just one sheet, but if you need to automate the production of several PDFs, you need to be able to specify the name of the file in the code. The subroutine SheetToPDF presented below allows you to specify a single worksheet and the full filename for the PDF. I've included all the code and external declarations needed. The two subs at the bottom give examples of how to call SheetToPDF with the passed parameters.
'Needed to Read INI file settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
'Needed to Write INI file settings
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _...........................
Is it possible to set a formula or something up so that if a row contains certain information, then the row will turn a certain color? For instance, if cell B2 contain the number "3" and cell B5 contains the number "5", then row B will turn yellow. Then if cell C2 contains the number "4" and cell C5 contains the number "7", row C will turn blue. This is just an example. In this example, I'd like a formula all the way down column's 2 and 5, but only the rows would change color (not columns) according to their information.
View 9 Replies View Relatedregarding this article:
[url]
Is there a way to name a tab with some combination of 2 or more cells?
A1="first"
B1="last"
so, the tab name would be "firstlast".
also, can a space be inserted?...i.e. A1&" "&B2
I have a column with random figures numbering upto 2000 meaning 2000 cells starting from 1 - 2000 and this varies every time I update my sheet....
What is require is that in C5 i should be able to set a max limit of "reference cells", the purpose of this limit is that it would automatically go like a stop watch from 1 until the limit... So for example if I put in 500 in C5 it would reference going up from 1 until 500, I need the highest random figure within those range of cells referenced.
These max results are referenced in a mathematical alogrithm of which the calculated result must match the base level or get as close to it as possible...
I would like to create a pop up message that would automatically come up when someone opens one of my Excel workbooks. Essentially, I want to have some text regarding the confidentiality of the document, then require the user to click an "OK" button to continue.
View 9 Replies View RelatedIs it possible to have Column E automatically enter a value based on a 4 digit number inserted into Column C?
I have about 200 different 4 digit numbers that can be entered into Column C and 8 different corresponding values that need to appear in Col E... Would this be possible / difficult?
I am trying to create a web query that will automatically collect information from a dynamic web page. There are five tables on the web page that can appear. However, sometimes there are only 3 or 4 tables that appear.
The web page automatically updates every 60 seconds so I would like to have a web query check the site every 55 seconds.
I can do a query and get the information but I have to do it manually.
I am trying to create a program that when a cell changes to a specific text from a pulldown menu of two items the active sheet's tab changes color depending on what is selected. Is this possible using the pull down menu, and how would you go about doing this?
View 9 Replies View RelatedIs it possible to get a row highlighted just by placing your cursor on top of any cell along that particular row?
View 9 Replies View Relatedprovide an input (1,12, or 123 would be the only input selections), then the vba needs to hide all rows that are not equal to the input
I have developed a type of calendar in excel with the following setup. There are five key headers and each header has a code it pertains to, either 1,12, or 123. The spreadsheet is set-up like so....
Column
A B
Key Project
12 A
1 B
123 C
12 D
12 E
123 F
1 G
So basically vba code that will produce a pop up and ask for a key then after the key is entered it will display rows that have an equal key in column A and hide all other rows.
Fromt he above ex. if "12" was entered the spreadsheet would show:
12 A
12 D
12 E
I work with worksheets in excel that usually have dollar amounts with 2 digit formulas. I would like to automatically round all the columns I specify to the nearest whole number. Presently, what I do is to create a new column, and then put in the following: =round(A2,0). Is there a way, I could highlight the whole column and then use the round function for everything at a time?
View 7 Replies View RelatedI have a coloum of data which is currently updating of 4 digit numbers so something like col 1 would be 3423 col 2 would be 6543 and so on and so on. This list will be constantly growning is there a way to constantally sort these into numerical numbers lowest first? The data would be on coloum B the other sort range would be in coloum a.
View 3 Replies View RelatedI have inherited a Spreadsheet/Data Base.
In my Worksheet I have 3 Headings: Contractor Supplied/Installed; Principal Supplied/Contractor Installed; Principal Supplied/Installed. Each heading has 10 rows below. In the Rows, spread over merged cells (inherited remember), are 3 columns:Item description; No. off & Remarks. When I print this I get an A4 sheet with 30 Rows and perhaps only 2 items in one of the Headings.
What I want to do, is to have initially 2 empty Rows per Heading shown. Then after entering data in Column A, Row 1, another Row automatically inserts below the empty Row.
I envisage there is always 1 empty Row. If that is possible I can minimize the use of paper and have 6 Headings on 1 sheet.
I want to the "No. of times taken out" column to automatically update if the status is "Borrowed".
So, say book had been borrowed 13 times, and the Book is borrowed again, I want the "No of times taken out" to change from 13 to 14.
