Is there a macro that will format unused rows or with no value in a given range. I have a sheet that I am using that some of the date is either added or deleted and would like to have a macro to do grey out unused rows. I am using this with a form control.
I have some if statements for 300+ employees. Some rows are left blank b/c a specific type of employee didn't meet the if statement criteria...which is what I wanted. Now how can I get rid of those blank rows (that actually do contain formulas but are returnnig a "" value) to have all of the employees in a "nice" grouping?
Apparently Excel can't delete unused rows, nor can it specify the sheet to be exactly X by Y large. My problem comes in any time I fill down a column of data by selecting the column header (A for example) and selecting fill down, rather than selecting the exact range of cels I need the formula in (which might be several thousand...) Excel winds up taking my sheet that was, let's say, 1000 rows long and filling down until now it's many many many thousands of rows long, with "error" fills in most of the rows. This now means that any kind of sorting, resizing of rows, etc. takes absolutely forever because it's dealing with 5 or 10 times more rows than I am actually using. Even clearing the contents of all the extra cels does not help. I can't in any way seem to get rid of these extra rows.
trying to create a button to hide unsed rows, can anyone help? basically the sample below is what i want to do but it hides all the rows listed.. i want it to hide only unused rows in the rows selected if yes is selected and unhide them when no is selected...
wannahide = MsgBox("hide rows", vbYesNo) If wannahide = vbYes Then Rows("5:28").Hidden = True Else Rows("5:28").Hidden = False End If
Is there a simple way to hide rows and columns? A lady in my office just asked me how to make only Columns A to J visible and only show 100 rows. She doesn't want the rest of the spreadsheet to show. I know how to manually hide columns and rows but it takes a while to drag down and select 64000 rows so I thoguht I would ask the question:
Does Excel have a feature that will automatically hide all unused rows and columns or is there a simple VBA code to do this?
I have a spreadsheet ranging from A1 to AH602 Column AH contains row totals and Row 602 contains colunm totals. I am importing a CVS file starting at cell A4 - the number of rows contained in the import varies drastically. I would like to build a macro that would remove all the unused rows. I have tried to record a macro that would do this but the number of rows always ends up static and not variable.
What I'm doing below is Using an END+HOME to take me to the furthest point in the active range then 3 UP ARROWS to get to the desired row then an END+LEFT ARROW followed by END+UP ARROW and 2 DOWN ARROWS to get to the starting cell in Column A. At that point I use a SHIFT+END+DOWN ARROW to capture the range of Rows I need to delete.
Sub Macro1() ActiveCell.SpecialCells(xlLastCell).Select Range("AH599").Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Range("A101").Select Range(Selection, Selection.End(xlDown)).Select Selection.EntireRow.Delete End Sub
This works except that the starting cell remains the static.
I like the look of when only the used columns and rows are shown. I like to hide all unused columns and rows, and have the background and a minimalist spreadsheet.
HOWEVER, is it just me, or does Excel move a lot slower when thousands of rows and columns are hidden? Particularly, opening files seems to be slower. I'd love to delete them entirely from existence, so Excel only has a few rows and columns to work with, but that doesn't seem to be an option.
I have a long grocery order list and only choose about 100 items out of 1000. I would like to send it to the store with only the items I need. There are several category headings throughout the list which I would like to keep...like Produce, Meats, etc. The list total dollar amount is automatically updated as I enter the amount of the items I need. I just have a lot of unused items (10) pages worth.
Need to find out how I can restrict a worksheet to show only the columns and rows that I want. For example, I want to show columns A to H, and rows 1 to 25, and nothing else - I don't want there to be a column G, or a row 26, just blank grey space. I know it's possible, because I've seen it done : D . But the closest I can get to is: View > Page Break Preview, which isn't quite what I'm after.
i have a form control listbox (list box 5), it is multi select, i need to create a for next statement that loops through the list in and tells me which "row numbers" as it were are selected. i.e if the 1st and 3rd ones are blue, it returns 1,3 in a cell? (lets say cell A1).
I am trying to use the Chartspace object on a VBA form in Excel 2002, but am unable to find out how to specify the speadsheet data to be used for each series. I have found out how to add series, and to add titles & legend etc.
I have many Form Control Check Boxes that all link to another sheet on row 3.
I have many changes to make but only want to implement the change related to the check box.
This code works perfect when you manualy type true or false on row 3 but not if the check box makes the change.
Private Sub Worksheet_Change(ByVal Target As Range) ThisCol = Target.Column If Target.Row = 3 Then RESULT = MsgBox(Cells(1, ThisCol) & " = " & Cells(3, ThisCol), vbOKOnly, "CLICK RESULTS") End If End Sub
Why does this not work when a check box changes the value in row 3?
I have several files with form control buttons that automate functions but, on occasion, they get smaller relative to the worksheet they're in. At some point, they become unuasable unless they're manually resized.
I have a rather large sheet with lots of embedded form control buttons, each one with a document link. Is there a function (no VBA) to search for a specific form control button in excel? for example: i want to find the form control button which is linked to the "application" document.
Does anyone know how to make a form control (ex combobox, textbox) have multiple columns so that it would behave like 4 controls in one. What im going for is a control that looks like the control used when setting windows system time "12:30:00 AM" So "12" is in col 1, ":" is in col 2, "30" is in col 3, ":" is in col 4, "00" is in col 5, and "AM" is in col 6.
I am looking to connect 2 form control boxes and have the second box run 2 different types of macros. The first box will have only 2 options - select by week and select by month.
The second box should show the list of weeks or months based on the selection in the first box. Then for the second box, if weeks is shown, a week macro should be run whenever a week date is selected. Similarly for the list of months, a month macro should be run whenever a month is selected.
I have been trying to do this for more than a week (after posting on this board) without success. I apologize if this seems like a duplicate post.
I need to extract proper unit price of a component from a large data base. So far, I have created drop down lists so that the users can select different parameters for each component. How do I use the user selected parameter to pin point the proper unit price from the large database?
I am working on an Inventory control worksheet where i have all parts used in a certain assembly on the left (A) followed by Qty per assembly (B) and then current baseline Inventory (C). In columns D-max i will have at the top a pull down menu to let the user decide if they are subtracting from inventory (Purchase Order) or adding to inventory (Fulfillment). Under both circumstances, depending on which is selected I would like a different form to pop up which allows the user to enter values to control the chart.
If Purchase order is selected then a pop up will ask the order number, date, and quantity. This will then fill in three specific cells in the chart which control an equation to subtract the number of parts based on the order quantity.
If Fulfillment is selected I would like a pop up or the entire parts list to appear with a field to enter the number of parts being stocked. The program will then add these parts to the previous inventory entry.
In the example attached you can see that right now you have to manually enter the number of units ordered, then an If statement takes over to calculate the new inventory level (If statement used to keep things neat and possibly to incorporate fulfillment at a later time). At this time there is no way for me to enter stocking transactions.
I have made an excel sheet for multiple choice questions, in which person needs to click on submit button after completing with the test & pop up will say "Your score is__". Now, I want to want to add one more button named "Reset". I want below :
1. Person should not be able to make any changes after clicking on "Submit" button.
2. If one tries to click on "Reset" button, it should ask for password.
3. After keying password for "Reset" button, all questions should be reset so that next person can give test.
I have attached an example excel sheet for reference : DoM1.xlsm
I am having some trouble with cascading two tier drop down. I cannot seem to find out how to do it. I am trying to create a two tier cascading drop down in form control. I want the first drop down to be Products we have and the second one to be customers. I also want to be able to create a bar chart from these drop downs where after we choose our customer by product, it shows the revenue we have recieved from the customer by months. Is there a way to do this? I figured out how to do this with just one combo box, but I am unable to do a cascading one.
I am designing (badly!) an excel file that is intended to be as user friendly as possible. I would like to add in a navigation feature using a list box or other appropriate control form that will take the user to the correct tab in the workbook depending on which item they choose from the list.
My aim is too hide all the tabs at the bottom whilst not crowding my page with multiple command buttons, and still be able to navigate easily through the workbook.
Is it possible to configure the workbook so the user can view the data in the worksheet from the form and I can hide the actual worksheet so that the data in the workshhet cannot be changed? Or maybe there is a better way all together??
I am attaching a copy of the worksheet.
Second, Is there a better way to input a code that would allow a search for duplicates in certain fields to be flagged (other than the color coding?)