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.
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?
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?
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 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.
if I can gray out the unused columns. I am creating a template that use only column A to J. I don't want user to be able to see or scroll to the unused columns. Is there any command in the setup that can gray out the unused columns? or maybe VBA codes?
I am trying to create a Purchase Order Form that will automatically pull the next unused number from a summary Purchase Order log. I would like to create a formula that will look for the next blank cell in Column B and then pull the number from Column A in the same row.
Other than using the search on a 1 by 1 basis, is there an easier way to locate Declared variables that are unused in the code and/or Public variables that are only used in 1 sub/function?
I've just finished my project (with loads of help from you guys) and wondered if there was any way of making it look less like the spreadsheet it is when it's opened.
I've removed everything I can and I'm left with a vertical scroll bar (needed) and the top menu bar (not needed).
Is that about it or can I get rid of that, and maybe size the window accordingly when the workbook is opened (so the dead space on the right is not seen) and perhaps stop the vertical scrolling at the bottom of the sheet so it doesn't scroll down to 65555 and the dead space under the sheet isn't seen.
I am using bar chart to display top 5 units sold each year to my Excel file. In the data table, I have listed all the units and its number (quantity) sold. The problem is, the legend of the chart displays all the units instead of just displaying units that has values (top 5 units each year) which makes the legend hard to read (see attachment) How do I get Excel to select legends only if there is a value for it?
I have got a spreadsheet which is being used as a database. I would like the spreadsheet to close automatically if the user has not "touched" it for a specific amount of time (say 1hr).
2 scenarios:
a/ The user is working in Excel but with another workbook. b/ The user has the excel spreadsheet open but is working with another application, say Word
I experimented with Workbook_WindowDeactivate but I do not know how to stop the process if the Workbook is reactivated (Maybe a DO ...LOOP calling a function returning a True/False statement on the event Workbook_WindowActivate !!). My way of thinking is the following
Workbook is deactivated Start a timer If the workbook is not reactivated with 1hr, save and close (no user intervention wanted) Else stop timer Repeat process
I use all of column A, B, and C, but i only use D1 to H44 other than that. Is it possible to hide everything to the right of Column I and below D44:H44, but leave the scroll area for columns A B C able to scroll to the bottom?
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5 123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5 123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
I have the following codes to delete all blank rows in column A
Dim lastrow As Long lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row MsgBox lastrow
With Sheet1 For t = 1 To lastrow If Cells(t, 1) = "" Then Rows(t).Delete End If Next t End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5 123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5 123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5 123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5 123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5 123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
Need to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).
Sub Delete_invalid_rows() Dim i%, j% Dim Nr%, valid As Boolean, BYPdata As Boolean Dim ar1 As Variant Dim ar2 As Variant Dim ar3 As Variant Dim ar4 As Variant Nr = 20 ar1 = Array(11, 14, 19, _ 20, 22, 25, 26, 27, 28, 29, _ 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................
