I want to be able to select a range on my spreadsheet, click a button, and have everything that is not selected hide itself. I've tried using intersect, but I'm not sure how to (quickly) loop through all of the columns and rows to see if my range is contained within.
View 2 RepliesI have recorded macro.
What I am trying to achieve is ....
When I select cell A2 and press command button Then - Columns C, D, E are unhidden
-Relative cells in selected row ( in this case C2,D2,E2) change font to 12
- When command button is pressed then C,D,E are hidden and font size goes back to 1
similarly if i select A3 same should happen to C3,D3,E3
Currently all is OK but when I press command button it all happens with the entire column C,D,E
Sample book attached.
Code is as follows:
[code]....
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
My boss wants me to create a macro to hide columns if there is a "X" on top of the column labels. The macro should be in toggle mode whereby the next pressing of the macro will unhide the hidden column.
There are more than 50 columns in my actual worksheet. The macro has to check column by column whether there is "X" marked on top and hide it if so.
The next pressing of the macro should then unhide all the hidden columns.
I have multiple departments and on each department we report the costs in four columns this month, budget, last month and last year. I have summed across the row to a hidden column and if the value is 0 I have a basic macro that "hides" the zero value rows. I understand I could further automate the process by having an "event" based in the spreadsheet which when the calculated values change the macro will run. This would mean when looking at the next department the zeros would automatically be hidden. I suspect I need a worksheet_calculate event but not been able to make it work.
View 2 Replies View RelatedI'm trying to create a macro that allows users to hide or unhide selected rows, either via toggle or userform. The macro I have looks like this, but for some reason it doesn't hide the rows:
Sub Hide_Range()
Dim UserRange As Range
DefaultRange = Selection.Address
Set UserRange = Application.InputBox _
(Prompt:="Select Range to Hide:", _
Title:="Hide Range", _
Default:=DefaultRange, _
Type:=8)
Rows.Select
Selection.EntireRow.Hidden = True
End Sub
what's wrong with my code and how I can further develop it to let the user decide whether to hide or unhide the selected rows?
I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.
View 2 Replies View RelatedI want to hide selected sheets and when i want to unhide them, ask for password to unhide the sheets.
View 10 Replies View RelatedI have a workbook with over 70 tabs whose position shouldn't be changed. Some of these tabs are colored in yellow (sorting by tab color is not allowed). I need to select these yellow tabs first and loop through them (only yellow tabs) and hide empty rows in the range of A1: G 50 on each of them. Grouping sheets wwon't work because each tab has different last row with data within that range.
View 6 Replies View RelatedCan you ctrl-F for only highlighted cells? I want to change all cells with '#VALUE!' in them, however, I only want to do this for a few columns.
View 4 Replies View RelatedI'm trying to format a new spreadsheet and was wondering if there was a way that I could stop the search function (normal Ctrl + F) looking through all cells, and only looking through column A? It has to be permanent, so I couldn't only highlight the ones I need when searching, and I can't use VBA due to security risks.Is this even possible?
View 7 Replies View RelatedCan you help with how to import into vba just the data in the currently selected cells?
What I want to do is to manually select a range of rows (in a single column) and upon running the macro put the data from each cell into the uppermost cell with each seperated with a comma.
I can manage the combining of the data but don't now the selection is read into VBA.
i try do function, which will be search/monitoring in selected cell (colored – yellow, orange, blue) and display (in green/results column) the last value in row colored/selected cell (first value from right side – results will be only for row).
View 14 Replies View RelatedI have a worksheet which will take a long time to recalculate. So, I want to recalculate only on selected range.
View 2 Replies View RelatedI want to transport the other column that they not changed rows that I have been selected somerows at the column.
For ex.
at Column A
Rows 2,4,7,9,22,34 .all select cells are going to transport column B.But select rows are not going to change.
How can I count the number of selected cells on an active worksheet? The number of rows and columns that a selected cell can be on change dynamically.
View 2 Replies View Relatedfor a small online database I have a column that lists nationalities:
French
Spanish
American
Latin American
(etc etc)
I need to add a prefix to all nationalities, for example:
Artist Nationality///French
Artist Nationality///Spanish
Artist Nationality///American
Artist Nationality///Latin American
(etc etc)
Is there a way I can select the 700 cells and do this in one shot? I can't add formulas because I will have to paste all this in text pad and then upload it.
When you are on a spreadsheet and you use the arrow keys on the keyboard to move from one cell to another and you can see the cell you are on because of the lines, is there a way to have the color of those lines a different color than black? I would like to be able to arrow from one cell to another and have the lines be red or green so that I can see easier which cell I am actually on. Sometimes it can be hard to see which cell you are. Even if I could make those lines bolder to show up clearer which cell I am on.
View 9 Replies View RelatedIs it possible to select specific cells and then have the Replace feature only apply to those cells, particularly if I use the Replace-All button?
View 4 Replies View RelatedI wanted to average selected cells excluding the value zero. Also, I wanted to make the value of cell is zero and not to display the error #DIV/0! if the denominator is zero.
A1 = Denominator
Selected cell:
A3, A5, A6, A8
I'm sure you've all encountered that amazingly annoying glitch where you click on one cell and 4 or 5 others highlight with it. F8 does not do the trick to stop it. Sometimes changing the zoom will work to get it to stop, but not always (like now).
I shouldn't have to arbitrarily change the zoom on a sheet for Excel to start working properly.
Just started using Macros on Excel.
I recorded a Macro that would take a value of a particular cell and add that value to the selected cell in the same row. However, this macro just runs on one cell and I want it to run on the entire row.
This may seem like a trivial issue but I've been racking my brain for the last couple of days to figure it out.
Here is the VBA code of the Macro I recorded. It adds the cell value to another cell with formulas already in it as you will see below:
Sub SpreadingTest15()
'
' SpreadingTest15 Macro
'
'
ActiveCell.FormulaR1C1 = _
"=IF(MarRF!RC9=""Trade"",IFERROR(IF(RC12=""BL"",RC13/12,INDEX('Variable Data'!R4C2:R16C17,MATCH('1410-Rev'!RC14,'Variable Data'!R4C2:R16C2,0),MATCH('1410-Rev'!R14C,'Variable Data'!R4C2:R4C17,0))*RC13),0),IF(RC14=R14C,RC13,0))+(RC35)"
ActiveCell.Select
End Sub
I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.
[Code] ........
Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'
'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.
I have a table with 2 columns A & B and desired outcome in column C (see attached sample).
I need to assign the same value to cells (in column A) located within the same group (in column A) using the following rules:
If any cell value within the same group = W220 or W210 or E240 or E250, then assign value "Group A"
If any cell value within the same group = P210 or C100, then assign value "Group B"
If any cell value within the same group = N230 or N250, then assign value "Group C"
The VBA code (in the code window) runs nicely on the range B10:B1000, but I'd prefer that it only run on a range I define by the cells that are currently highlighted/selected on the active sheet. How should the line of
Set SHOPS = Range("B10:B1000")
I have the following code in a macro of mine which checks each cell has something entered and if not gives the message box.
View 5 Replies View RelatedIs it possible to show gridlines in some cells and hide gridlines in others on the same worksheet? Is it also possible to isolate a row or column and change the size of selected cells without changing the size of the cells in other rows and columns?
View 5 Replies View Relatedthis is my first post in this great forum. let me start talking about what I need, I used to convert some statement from PDF to XLS format for my work purpose. sometimes I am facing a problem that some text in the statement will come in more than one row, actually I need it to come in one cell but it is coming in several rows that mean, if I want to sort or filter my data, I will lose some important data. What do I want???
I want a macro to merge the contents of selected cells and put it in the first cell of the selected cells then clear the other cells. I will use this macro for the following cases: Case 1- assume we have this data in any sheet.
B1=How
C1=are
D1=you? ( all without any space )
after selecting the said cells and applying this macro, the result will be:
B1=How are you? ( one space only between the content of the selected cells )
C1=D1=empty
Case 2- assume we have this data in any sheet.
B1=How.........
Is there any way we can append the value of selected cells to make it 'value', Basically I need to add ' at the beggining and end of the a value in cells and a comma (,) at the end of it.
Example I have value 12345 in cell A1, When I run a macro by selecting cell A1, it should change it to '12345',
It should apply for all the slected range.
Column A = list of names
O1:P20 MAY contain names
If A1 = Fred
AND O5, O16, P3, P19 = Fred
Selecting A1 changes all instances of "Fred" to RED-BOLD
If A2 = Felix
AND O9 = Felix
Selecting A2 changes "Felix" to RED-BOLD
Etc...