Active Area
I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area.
Sub Insert_for_SQs()
Do
Selection.EntireRow.Insert
ActiveCell.Offset(6, 0).EntireRow.Activate
Selection.EntireRow.Insert
ActiveCell.Offset(12, 0).EntireRow.Activate
Loop
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sort Area Around Active Cell By Two Columns
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy. Dave supplied me with a macro that will sort a range by the first column: Sub SortThis() With ActiveCell. CurrentRegion .Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With End Sub I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
View Replies!
View Related
Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints ! Is this possible? FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells. I used this code from a post on a similar topic, but excel is complaining code in bold Private Sub Print_Area_Click() Dim lastCell As Range Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0) Do Until Application.Count(lastCell.EntireRow) 0 Set lastCell = lastCell.Offset(-1, 0) Loop ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address End Sub
View Replies!
View Related
Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart: 1) Weather data (temperature) by region: So one region, would be: Northwest I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month. 2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region. 3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data). What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
View Replies!
View Related
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null. I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it. I've attached the spreadsheet so you get a better idea of the problem that i encountered.
View Replies!
View Related
Specifying Area Of Worksheet
1)I am trying to specify the area of a worksheet, for example columns a-z and rows 1-1000 but I am having great difficulty in doing this. I have tried selecting the last row and shift>end>down, Edit >all then Edit>delete but nothing happens. The screen just flickers and all the rows are still there. Even specifying the print area does not do it. Any answers? 2) Any ideas how to sum a datalist by text in a box? I.e if there are 1000 rows selected how amny are filled. If there is a way of selecting 'any text' this would work but there is no constant value/text to use. BTW I am using Excel 2000
View Replies!
View Related
Print Only Used Area
I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page. I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish.
View Replies!
View Related
Print Area Always Changes -
I use the code below to print a portion of a sheet. My problem is that the area always changes. The $A$476 should be the first cell in that column that contains "HEA" The "536" in $F$536 is the last row that contains data. There are conditional formats all the way down to row 1,000 but not data Private Sub CommandButton1_Click() Dim strCurrentPrinter As String, strNetworkPrinter As String ****strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF") ****If Len(strNetworkPrinter) > 0 Then ' found the network printer ********strCurrentPrinter = Application.ActivePrinter ********' change to the network printer ********Application.ActivePrinter = strNetworkPrinter ********ActiveSheet.PageSetup.PrintArea = "$A$476:$F$536" ********ActiveSheet.PrintOut** 'print something ********' change back to the previously active printer ********Application.ActivePrinter = strCurrentPrinter ********ActiveSheet.PageSetup.PrintArea = "" ****End If End Sub Obviously there is a function also that finds the full network printer name, I didn't think it was important, because my issue is the print area. Everything else works fine. If you need it let me know and I will post it.
View Replies!
View Related
Variable Print Area
I'm using Excel 2003. I have a spreadsheet used for costing jobs giving a detailed breakdown of parts and time. Sometimes the customer requests a copy so I'm creating a macro for my boss that prints the spreadsheet, less the hidden columns that I don't want the customer to see. This I can manage. My question is this: Is there a way within the macro to set the print area to change to where the last cell is automatically? Each spreadsheet is a different length dependent upon the work required and setting a larger print area to compensate would use excess paper and ink.
View Replies!
View Related
Area Chart With #N/A Data
Currently I'm working with an amount of data that corresponds with one data point every day in one year. Not every day is measured thus nothing is entered, some days can also be 0. Therefore I've made an if statement in the column next to the raw data column with an if statement returning #N/A if a cell contains nothing or 0. This gives me normally no problems when I want to make a Line chart, excel just skips the #N/A cells and draws the line to the next data point (that's the whole reason for the if statement). However this is impossible with area charts. I can understand why, but I still want it as nothing or 0 in a cell not necessarily mean that nothing happened.
View Replies!
View Related
Finding The Top 3 Of Each Area
I have a list of tons of items that have lost orders against them. The items themselves are catagorized in 11 different areas and I'm trying to find the top 3 of each Area. These items are sorted descending by the number of lost orders and will constantly be changing every day. Here is an example of how my workbook is set-up: A B C D E ....
View Replies!
View Related
Print Area Dimension
I used the macro recorder for this: ActiveSheet.PageSetup.PrintArea = "$A$1:$P$246" How would I change this to use variables? I need to set the print area to A( FR ) to P( LR ) Of course FR is first row, LR is last row
View Replies!
View Related
Auto Print Area
I have a hard time transitioning from other examples to my workbook. What I am after, is for the print area on 2 sheets to be set by the count of non-empty cells in column B on the first sheet. I have a maximum number of entries of 200 (rows) plus a header row. So, my entry #200 is in worksheet row 201 (cell B201) In cell B203 I use formula "COUNTA" to tell me how many cells are not empty. As an example, this number might be 36. The print area should be limited to worksheet row 37 (1 more than 36 due to the header row). My columns are always the same, A thru I. On a second worksheet, measurements are recorded, 10 per row. So, if I have 36 items to be measured, I use 4 rows (10 per row=3 full rows and 6/10 of the 4th row). I would like to have the print areas set automatically, based on this number in cell B203 (see above notes). On the 1st sheet (using example of 36 entries), my print area needs to be thru row 37, columns A thru I. On the 2nd sheet, my print area needs to increase 10 rows every time B203 increases by 10. (this 2nd sheet has other info on it, so when the print area increases, it needs to go in steps of 10 rows. as an example, if B203=8, then the print area on the 2nd sheet includes cell A1 thru cell K13, but if B203=12, then the print area would be cell A1 thru cell K23)
View Replies!
View Related
Paste Area Too Large
i am trying to first of all find the column that Alias appears in the first 3 rows and then paste that entire column to a new sheet. most is working. however i get an error 1004 saying the area i am pasting to is not large enough or the wrong shape. Private Sub CommandButton5_Click() ' ' merge Macro ' Macro recorded 6/7/2007 by Ray O'Connell Range("A1:K3").Select With Selection .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False...........
View Replies!
View Related
Calculate Polygon Area
I've been working for awhile now on vector-based geometry/trig and so forth. I'm very interested now in developing a solution to the following: Given pixel coordinates for an n-sided polygon (concave or convex) calculate the pixel area of the polygon. My problem is, I don't have time with my reg'lar job/duties to investigate this and find the solution quicly. I was hoping perhaps some of you smarter-than-the-average bunch may already know the answer and could bring it to me a little quicker. If no one should respond, I'll let you know when (or should I say "if") I figure it out... but unfortunately, it might be a few weeks before I can get to it.
View Replies!
View Related
Print Area With A Macro
I am looking to set the print area with a macro, below is what I had hoped would work. It does not. I get unable to get Vlookup property of worksheetFunction class error. Sub Printzed() Start = Range("C6").Address Finish = Res = Application.WorksheetFunction.VLookup(I5, Range("prlu"), 2).Address myPrintArea = Start & ":" & Finish ActiveSheet.PageSetup.PrintArea = myPrintArea End Sub
View Replies!
View Related
Print Area VB Code
I wish the print area to be set based on the number entered into cell "D58". My code so far is: Sub Worksheet_SelectionChange(ByVal Target As Range) Dim NumPages As Range Set NumPages = Range("D48") Worksheets("Sheet6").PageSetup.PrintArea = Worksheets("Sheet6").Range(0, 0).Resize(NumPages * 21, 47) End Sub
View Replies!
View Related
Specify Area To Delete Using Userform
I have very little experience with Visual Basic and seem to be having trouble with what I think should be a fairly simple matter. I want to add a control to a userform that allows the user to select a range of entries to be deleted. The user can稚 directly modify the spreadsheet as it is protected. The only way for them to change it is via the userform. The way I want it to work is when the control is selected a form appears in which the first and last rows of information to be deleted are specified. I don稚 want to delete the whole row, just the first 7 columns of the specified row. Here is the Code that I have so far:
View Replies!
View Related
Calculate Area Under The Curve
i am getting some data via RS 232 port and plotting it straight to excel, which comes out to be in forms of waves you can say sine waves, now i need to calculate area under the curve, say like if i highlight some area it will automatically calculate the area, or if i select two points on the curve it will calculate the area. please help me out i am really stuck at this part.
View Replies!
View Related
Print Area Code
I have some code to select a print area depending on wether or not there is data in a cell. The code I have so far partially works but not quite like I need it to. Here's what I have, Sub selectprintarea() Select Case True Case Range("C5").Value > 0 Range("A1:M47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$M$47" Case Range("Q5").Value > 0 Range("A1:AA47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$47" End Select End Sub It will select the print range if there is data in C5 but if there is data in C5 and Q5 it only selects the first print area. I know I need an "If - Else" or something of that nature but I cant figure that out. Also I need it to look at other worksheets not just the active one. I've tried a replacing the ActiveSheet with Worksheets (Sheet1). but that gives me an error.
View Replies!
View Related
Scrolling Area Code
I have a sheet in which I want to limit the scrolling area to B2:B60 & D2:H60, in other words I don't want to include neither A2:A60 nor C2:C60 in my scrolling area. Is there a way to do this? I know I can lock the cells, however, pre-existing code will not work if cells are locked.
View Replies!
View Related
Dynamic Print Area
I can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup. The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.
View Replies!
View Related
Remove Selected Area
I need to worked out Hours worked in a timesheet. This was the easy part, the hard part is the clause tha HR threw in, which is: If you have worked and 8 hour day WITH 1 hr lunch then you qualify for overtime. if you work a 8 hr day and work through your lunch (1 hour) (so equivelant to 9 hrs) you still do NOT qualify for overtime there for Overtime = 0. This is cause some people work though their lunch to get overtime, but legally they have to have a break so we are not paying overtime for it. I have basically tried in a formula to replicate this but it works with some data and not with all. attached is an example, as you'll see the formula works in some cells, but not others.
View Replies!
View Related
Return Area Code For State
I would be loading data into a spreadsheet, and when there is a phone number I'd like to have it populate the state field. (ex. (212) 123-1234 in the phone field, would populate "NY" in the state field, is this possible? If so is there any brief way to describe how to do so without taking too much of your time?
View Replies!
View Related
Using Macro To Set Print Area
My macro selects cells containing data - then i want the macro to set the print area to those selected cells - works fine - but the selected cells won't be the same each time i run the macro. Here's how it looks now: Range("A1:M1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.PageSetup.PrintArea = "$A$1:$M$76" The next time i run the macro the selected cells won't be A1:M76 - how do i have the macro set the print area to whatever is selected?
View Replies!
View Related
How To Auto Set Print Area
if there is a way with functions or code to automatically set the print area. I would like to have it set to start at A1 and print to column G & the first ROW with a zero value in column F. The ROW in which the first zero value appears will be the only variable. It will always be column G.
View Replies!
View Related
Group Shapes In A Certain Selected Area
I don't think this is possible, but I thought I would ask. I have buttons on a sheet. Then I have drawings, that I make, using different shapes. Instead of selecting all shapes on the sheet, I want to just select the shapes in a range say B17:F28 so I can group them.
View Replies!
View Related
Non-contiguous Print Area Macro
I have a document which will require signatures after printed and want to keep the signature portion at the bottom of the page (currently rows 102-104). The problem is that the document will not need to be that long for every person and was wondering if it would be possible to have a macro that checks (C7:C:100), stops printing when it reaches the blank cells, and then prints those last 2 rows?
View Replies!
View Related
Return Cursor To Predefined Area
I have a button in my worksheet. When clicked on I want to change the text in this button so far so good. I have however a problem how to return to the cell that was active, the moment I clicked the button. so far I have the following code
View Replies!
View Related
Print Area - Multiple Selections
Is there a way of selecting several print areas on several worksheets and printing it on ONE page? Selections will not be the same size. Also is there a way to lock your printareas? I would like to save the spreadsheet and open it up next month and the printareas are still there ready for me to print. It seems to reset after i have printed it
View Replies!
View Related
Adjust Print Area Macro
The macro below works perfectly except for one problem. I only want it to set the print area from Column A - Column M . It currently selects data i have in Columns N and beyond and i do not need that to print. From A - M is 13 columns.
View Replies!
View Related
Print The Area With Dynamic Setup?
My worksheet (excel 2003) contains lots of details and requires different page setup for printing e.g. (a) Cells B3 to R3 downwards: Data details, which user may add more rows of records (b) Cell S1-Z20: Data summary (formula) linked with (a) (c) Cell AA1-AK15: Pivot Table linked with (b) Therefore, I set buttons, linked with 3 macros to do page setup for the different ranges. However, I had some problems i.e. 1. For print area (a), how to define print area parameter in a macro to check the last row of data. i.e. check for last "blank cell" of column "C-E" (others columns contain formula before input the data) 2. In the future, if I or user insert new columns in area (a), is it possible to setup macro that have print area for (a) - (c) shifted without changing the parameter in the macro?
View Replies!
View Related
Set Print Area Through Code
I am having trouble setting print area on rows that can vary from 100 to 6000 rows. This code works good except that the print area cuts off the last row of data every time. "AreaBegin"=row 6 and "AreaEnd" = to last row after a set number of rows have been inserted based on a counter. Basically how do I get this code to capture the last row for print area? Sub SetArea() ActiveSheet.PageSetup.PrintArea = Range("AreaBegin", Range("AreaEnd").End(xlUp)).Address
View Replies!
View Related
Limiting Scrolling Area On A Worksheet
When I limit the scrolling area in a worksheet, and it works there and then, when I close and reopen the whorkbook, the limiting does not work. This is how I do it:- I Right-click the sheet tab for the sheet which I want to place a limit. In the resulting Context menu, I choose View Code. The VBA editor appears and asks for the password to view the code. I keyin the password I then set in my limit as B2:O25 Then I close the VBA Editor and display the worksheet where I try and I find it working. I close and save the workbook. When I reopen the limiting has disaapeared!
View Replies!
View Related
Automatically Round A Named Area
i have a file that people enter sales figures into each row. the numbers are sometimes beyond 2 demical places. how would i go about making a macro button, that when pressed, AwardTotal (name of the all the award cells in column e) is rounded to two decimal places. I then would like to sort all the rows that might be inserted, by column "C" (name field). people will insert rows, with row 11 being the first row with data. Row 10 is hidden, and row 9 is the header row. but the number of rows inserted will be different each time. I already have a button set up (that someone helped with here of course) that asks how many rows to insert.
View Replies!
View Related
Vertical Lines - Area Chart
I'm trying to create an area chart to show the change in the number of staff working in my store over time. Unfortunately, Excel likes to draw diagonal lines. That is if I have the following data: 9:00AM - 1 staff 10:00AM - 2 staff 11:00AM - 1 staff Excel will draw a diagonal line between each data point, rather than creating a box. This gives the illusion that there are somehow half people working at 9:30 and 10:30. Is there a workaround?
View Replies!
View Related
Find Last Column In Print Area
I need to use macro to find the last column index number within the print area. My cell sizes change depending on the data entered so the last column in the print area changes. The code I want to implement involves putting a title at the top of the page and creating a border that spans the width of the page. This is what I have so far: LastColumn = Cells.SpecialCells(xlLastCell).Column ' replace this with last column in print area Rows("2:2").Select Selection.Insert Shift:=xlDown Range(Cells(1, 1), Cells(2, LastColumn)).Select With Selection .MergeCells = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With
View Replies!
View Related
Table Inside Chart Area
trying to find a way to insert a table into an active chart area. unlike inserting charts as objects inside of a spreadsheet, i am trying to insert a table of the spreadsheet into the chart window. the purpose is so that i may have a numerical reference to my data. usually i post questions to which i have begun work on - but i have had no luck even brainstorming how this would be done! the ONLY solution i have surmised is to insert a table into a chart area as an Object(picture) but how would one write a macro for making dynamic range screen shotpicture files?
View Replies!
View Related
Set Print Area By Month
I'm trying to set-up the print area for my actual & Forecast. I have two years of data from A1 to U735, dates are in column B. Is it possible with a macro to set up the print area for each Month?
View Replies!
View Related
Calculate Flow Area Of A River
i have a river cross section data. for different flow levels in the river i need to to calculate the flow area. i have a very large series of flow level data for 10 years at 1 hr interval.can you give a solution of that.
View Replies!
View Related
|
TRACKER
