Removal Of Unused Rows (variable Range)

Oct 27, 2006

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.

View 5 Replies


ADVERTISEMENT

Assigning A Variable And Pasting Variable To Last Unused Column

Nov 19, 2008

to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.

View 14 Replies View Related

To Delete Rows Unused

Jul 13, 2007

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?

View 9 Replies View Related

To Delete My Unused Rows

Sep 24, 2003

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.

View 9 Replies View Related

Delete Unused Rows

Jun 21, 2007

I have a page full of data and have some rows in between them which are empty. Is there any way that I can delete the unused rows through VBA code.

View 6 Replies View Related

Button To Hide Unused Rows

Nov 21, 2006

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

View 10 Replies View Related

Hide All Unused Rows And Columns

Jun 28, 2007

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?

View 9 Replies View Related

Form Control To Grey Out Unused Rows

Nov 8, 2009

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.

View 3 Replies View Related

Hiding Unused Columns And Rows Slows Down Excel?

Mar 16, 2013

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.

View 2 Replies View Related

Consolidate Grocery Order By Removing Unused Rows

Apr 28, 2014

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.

View 2 Replies View Related

Removing/Greying Out Unused/unwanted Columns & Rows

Jun 21, 2006

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.

View 2 Replies View Related

Range Content Removal Not Working

Aug 24, 2012

what is wrong witht his code?

If Sheets("Workings").Cells(ExceptionCount, ColumnCount + 3).Value = 1 Then
Sheets("Allocations").Range(.Cells(ABIndex + 5 + week, (ColumnCount * 7) - 5), .Cells(ABIndex + 5 + week, ColumnCount * 7)).Value = ""
Else
End If

I know the If statement is working, but for some reason I can't fathom the line that removes the cell contents does nothing. I have tried ending with .ClearContents rather than .Value but that doesn't work either.

View 7 Replies View Related

Referencing Rows Using Variable And Intersect Range

Jan 18, 2009

I am trying to clear the contents in a rows reference by a variable (rownum) using the

View 2 Replies View Related

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

View 4 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View 5 Replies View Related

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this

View 9 Replies View Related

How To Gray Out Unused Columns

Oct 24, 2013

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?

View 2 Replies View Related

Populate With The Next Unused Number

Aug 15, 2007

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.

View 4 Replies View Related

Locate Unused / Declared Variables

Jan 11, 2012

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?

View 1 Replies View Related

Hiding Unused Worksheet Space

May 2, 2007

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.

View 9 Replies View Related

Conditional Formatting - Unused Columns

Jun 19, 2006

Is it possible to use conditional formatting to set all the unused columns (S - ZZ) interior color to gray?

View 5 Replies View Related

Set Range Variable Using Variable Row Number

Mar 11, 2008

I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.

Dim Totals As Range
Set Totals = [U37: AE37]

Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.

View 3 Replies View Related

Remove Unused Legend Values From Graph?

Dec 19, 2013

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?

View 2 Replies View Related

Close An Unused Workbook Automatically After A Specific Time

Apr 19, 2007

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

View 9 Replies View Related

Scroll Area And Hiding Unused Portions For Entire Workbook

Jun 27, 2012

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?

View 6 Replies View Related

Leading Zero Removal

Dec 13, 2008

I have cells where the formual is this: =D17/SUM(D17:E17). The result is always going to be a three-digit decimal, less than 1. .546 .345 .678 etc. Excel, of course, places a zero in the front: 0.546 0.345 etc. Is there a way to remove this?

View 5 Replies View Related

Command Bar Removal

Jan 30, 2009

I have just started playing around with events etc and i was trying to reomve the command bar using the code below in module 1.

View 4 Replies View Related

Removal Of Zero From A Number

Jun 7, 2014

I have about 80K (80 thousands) entries.Each entry starts with #000. I want to remove "#" sign and zeros as well.I had used "LEFT " function but problem is that some numbers have 3 zeros in the start and some have have 4 or 2 or one zero. I tried to do it manually but it proved a gigantic task and been unable to complete it.

The few examples of entries are as under.
#0001234
#000001897
#034567
#00671234

View 5 Replies View Related

Removal Of Spaces

Aug 8, 2006

Assume cell A1 contains a set of words. If cell B1 contained the following formula, the contents of cell A1 will be shown in B1, minus the last word.

=If(ISNUMBER( FIND(" ",A1)),LEFT(A1, LOOKUP(32768,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))),A1)

However, the space or gap before the word that has been removed will be present in cell B1.

eg. If A1 = "how are you", then B1 will = "how are ".

I need this space to be removed too.
ie. B1 = "how are"

View 5 Replies View Related

Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?

Feb 19, 2010

I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved