Usedrange Not Refreshing

Oct 12, 2007

I have a worksheet on which i use vba Usedrange to determine all the cells that are being used, but it only picks up all the range after the worksheet has been saved. It seems that the "usedrange" property only holds the actual usedrange at the last time the workbook was saved.

Is this correct?

If so, is there another way to "refresh" the usedrange property without saving the workbook.

If not, is there another way I can determine what the usedrange actually is at any point in time?

View 9 Replies


Looping Through Usedrange

Jan 10, 2010

I have a usedrange in a workbook, say a1:g150 - simple.

I want to loop through just the first row of this usedrange and delete any rows that are empty.

Now I am sure this is simple and i just cannot get my head working. I have managed to select the first row and loop through, but I dont want to 'select' I just want to loop.

How can i assign the range to a variable? I can get the number of rows via usedrange.rows.count, which would return say 150, but how do i then assign a new variable the range A1:A150? then I can loop by using "for each cell in newrange"?

View 9 Replies View Related

Usedrange Property

Apr 22, 2007

macros in excel XP.

I written up a marco code to do series of calculations for me. I have one program that will use different matrix size (say 5 rows by 5 colums for one set of calcuations and open up another, but with 105 rows and 35 columns)

the way it works is the user input data in row 1 and column 1 and the marco does the following codes-

Dim numrows As Integer
Dim numcols As Integer


thisworkbook. names.add "matrix",,,,,,,,"=!r2c2:r" & numrows & "c" & numcols

this code defines my range that will be used in the formulas further down the code.

the problem is that I test ran the code using the matrix=b2:ai105
and I was using the code for a run with matrix=b2:q77

but the code is giving me the range from the previous macro run (ie. b2:ai105) and want it to use the matrix based on new data size (say b2:q77).

I tried using activesheet.usedrange into the macro, but it does not work.

Basically I need to "reset" usedrange property so it uses the correct range every time.

View 3 Replies View Related

UsedRange.Offset Is Erroring

Apr 12, 2007

I am getting an 1004 error:

Application=defined or object-defined error here:

ws3.UsedRange.Offset(1, 0).ClearContents

Sub Importer()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Dim sPath As String

Application.ScreenUpdating = False

sPath = "C:Documents and SettingsDougDesktopTGSTGSFiles"

View 9 Replies View Related

Breaking Links And UsedRange

Jun 23, 2006

I am working on a project that involves copying data from one sheet to another, then breaking all links to the original sheet. This part of the program is working fine. The problem comes when I try to put borders around the used range of cells in the new sheet. I want the border to only go down to the last row that contains text in column A.

Currently, the program is putting a border around all the cells that have text plus those that originally contained links (all the links are not always used). So even though I have removed the links from the new sheet, usedrange still includes these extra rows. This is no good for printing, since the links spill over to page two and I dont want that second page to print if there is no data.

So I basically need to know how to fix the problem with usedrange, or instead need a way of specifying the last row that contains text in column A. I have done a search on this and tried a number of different things, however none have worked so far.

View 9 Replies View Related

Bordering A Range (draw The Border For The Cells With In The Usedrange From Column A To BM)

Sep 21, 2009

I would like to draw the border for the cells with in the usedrange from column A to BM.

I have the below code, that I was using to border the column D alone.
When i change the range from "A:BM", i dont get the intended output.
Can somebody tell, what modification, I should do to get the desired output?

View 5 Replies View Related

'vlookup' Not Refreshing

Nov 1, 2008

I have been working with one sheet (call it tab A) that has 40-50 vlookup formulas that reference another sheet (call it prices) within the same workbook. Everything worked fine. I change my prices tab and everything auto changes in tab A.

Now I copied 'tab A' 22 times creating tabs B - W. I need to slightly modify each tab and based on my prices data, print out the appropriate tab.

Problem is now, when I change my pricing tab, tab A - W do not update. F9 does nothing. Rebooted didn't work. If I go into the formula and hit F2 to edit & return it recalculates to the correct data. But I can't get it to auto update.

View 6 Replies View Related

Refreshing / Triggering A Cell?

Jun 13, 2014


I take this paste text only to get:


this wont work normally until i open the cell and press enter.

Any way I could do that for more than 1 cell at a time?

View 6 Replies View Related

Refreshing Protected Worksheet

Jan 5, 2013

I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.

In turn, the current information is displayed on a summary page so we can all see who's doing what.

I want to protect that summary page and its formulas from accidental amendment.

However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.

Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?

View 5 Replies View Related

Refreshing Activex Textbox

Apr 6, 2009

I've built a spreadsheet with quasi-animation for a physics class on projectile motion. In it there's a animated chart showing velocity change as a function of distance. The chart has on it an ActiveX textbox that refers to a cell that contains a formula showing "V_y = {velocity}". I've tried a number of things to get the display of the textbox to change during the animation. If I break the code at the line that updates the cell referenced by the textbox the display changes. But I can't figure out how to get the display to update while the code is running.

View 4 Replies View Related

Slicer Buttons Not Refreshing?

Dec 3, 2013

Im using slicers of a pivot table which has been generated from a sheet containing around 50 rows of data - not a lot of information. I deleted the information, then started re added information. I then refreshed the pivot table which worked but the slicer still contains information that I input a week ago. I deleted the slicer an cretaed a new one but again, it still contains data from a week ago even thought the pivot doesn't. How to make the slicer refresh to show contains of the pivot?

View 1 Replies View Related

Sum Product Not Refreshing On Opening

Feb 24, 2009

One of my SUMPRODUCT formulae doesn't seem to be refreshing when i re-open the excel file.


If i open the excel all the cells with this similar formula show as 0. If i press Shift+Ctrl+Alt+F9 then they calculate.

I have another SUMPRODUCT formula on another worksheet in the same book that seems to be fine.

I can only assume that the reason why it's not calculating automatically is to do with the TEXT "mmmm" reference, is that correct?

Is there anyway i can have it autocalculate so that if i send this as a report i don't have to ask the ppl looking at it to please press Shift+Ctrl+Alt+F9 when they open it :P

View 9 Replies View Related

Pivot Tables: VBA Refreshing

Oct 16, 2003

Is there a code I can use to update pivot tables e.g every 10 mins?

View 9 Replies View Related

Automatically Refreshing Macro

May 1, 2006

I have some code that downloads prices for me in excel. However, instead of running this macro everytime I was wondering if it is possible to write a piece of code that would run this macro every x minutes?

View 3 Replies View Related

Refreshing Linked Combobox

May 17, 2006

I have been working on creating a form in Excel. I have already figured out how to have combo boxes that are dependent on one another. Now I have a question on that. Let's say that my user makes a selection from the first combo box. Clinical Services for example. The second combo box should now show only the values that pertain to Clinical Services (which it does, that part is working fine). So let's say they choose Retail under Clinical Services. However, now let's say that the user changes their mind and they go back and change their selection in the first combo box to Mail instead of clinical programs. The second combo box stays on the selection that they had previously picked (Retail) even though that value does not pertain to their new selection (Mail). How do I refresh this to delete their old selection in the second combo box (Retail)? If they change their mind of the first combo box and select something else the second combo box does display the correct drop down information, it just doesn't clear out the old selection.

View 4 Replies View Related

Refreshing Pivot Chart Range?

Jul 1, 2014

I'm looking for a way to refresh my pivotchart and update my pivot table data every time that a certain macro runs. I could just delete it and make a new one everytime but that runs in to the problem of defining a new sheet every time. Is there a simple way to do this. say I my pivot chart is on "Sheet 7" and it there is only one pivot chart in the workbook and my source sheet is on "Combined Data". and at the moment the data range is "A1:N435" and say next time I run my macro the data range will add 20 rows of data and the next 25.

View 1 Replies View Related

Refreshing Upon A Change In A Tile? - VB Problem

May 25, 2009

I have a oval which changes color using the color given in a certain tile, temporary called "Hi" . However the oval doesn't change color automatically with the tile changing, it waits till the user clicks the checkmark for the tile, I'd like it to do it automatic, but it doesn't. Is is possible to animate this script a bit more so it refreshes upon a change in the "Hi" title.... ?

View 14 Replies View Related

Check If Excel QueryTable Is Refreshing Or Not?

Sep 26, 2011

How do I handle URL connections - such as logging into a web application?

Example 1
When I use .Refresh BackgroundQuery:= False and there is a problem connecting, Excel hangs ... cannot Ctrl-Break ... only option is to kill Excel (and lose changes).
global QT As QueryTable
Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & vLoginURL,


Now, if it's still refreshing, it's ok. But if not, it gets stuck on .Refreshing with run time error 424.

Ideally I want to use .Refresh BackgroundQuery:= False as I am calling a number of urls. I just need to provide an "out" when the url fails and hangs on .Refresh BackgroundQuery:= False

View 6 Replies View Related

Pivot Table Not Refreshing Properly?

Sep 18, 2013

I have a pivot table that I refresh using .refresh in the VBA code.

My problem is that when I loop through all the PivotItems in a filter list in order to make all visible, there are PivotItem names that aren't in the drop down menu and so the code breaks.

Do I need to refresh the pivot table slightly differently than just using .refresh?

View 1 Replies View Related

The Value In The Field Should Not Disappear After Refreshing A File

Apr 6, 2009

I have got this in a pivot table

Customer Total sales(USD)

30010 500.00
30041 2000.00

In another column I enter a comment for customer 30010(and leave the comment column for customer 30041 blank) as below
Data 1.xls
Customer Total sales(USD) Comment

30010 500.00 Test 1
30041 2000.00
Later on the content of the file changes as below:

Customer Total sales(USD)

30025 1500.00
30010 500.00
30041 2000.00
When the file is refreshed I would like to have comment as per the former file i.e the comment “Test 1” should appear against customer 30010 and similarly for any other customer that I enter the comment.It should be as below:
Customer Total sales(USD) Comment

30025 1500.00
30010 500.00 Test 1
Customer Total sales(USD) Comment

30041 2000.00

View 9 Replies View Related

Refreshing Protected Pivot Table

May 31, 2006

I've referenced a few threads this one in particular [Solved] Pivot Tables : Refresh on protected sheet

I've tried the example given in the link and it still does not work. I want to protect the sheets that contain pivot tables, but still be able to refresh them. When I try I get an error. I've tried this a few ways.

My first attempt was to protect the sheets using allowuserinterfaceonly at a workbook open event. This caused warnings as soon as the workbook was opened. Then I tried Dave's suggestion and I get a runtime error 1004: Cannot edit PivotTable on protected sheet.

View 4 Replies View Related

Pivot Table Preventing Workbook From Refreshing

May 6, 2014

I've got a workbook with a several pivot tables. For convenience, I've deleted the pivot table source and left the pivots "self-standing". Problem is now when I try to refresh the workbook, I get a "Reference is not valid" error which prevents the workbook from updating.

View 2 Replies View Related

Pivot Table Refreshing By Changing A Cell

Feb 16, 2009

How can I refresh a pivot table automatically by changing a cell content (say B2 which contains text, not value) apart from using the refresh button?

Cell B2 is not part of the pivot table.

View 6 Replies View Related

Pivot Table - Adding Data And Refreshing

Oct 17, 2011

Its been ages since I used a pivot table and I cant remember much.

I did one last week - lets pretend on the range A1, B100.

Ive since added some extra info into the range and it now goes from A1, B110.

I go to my pivot table and hit "!" and it updates. How can I be certain its included everything from my range? Is there a way I can check the pivot table to see where the range is from?

View 3 Replies View Related

Refreshing Lined Cells And Breaking Links

Oct 25, 2011

I would like to refresh all external links to other xl files (on a network drive) and break all links

I have a code like this:

For Each l In ActiveWorkbook.LinkSources
Workbooks.Open Filename:=l, updatelinks:=1, ReadOnly:=True
ActiveWorkbook.Close False
Next l

For Each l In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink l, xlLinkTypeExcelLinks
Next l

It seems to be working, but if i go step by step (f8) it updates values but sometimes after closing the source workbook and updating next one, the previously updated values disappear...

I cannot open all files because there are many of them and they are like 40mb each.

There are no defined names in the file, except print_area

View 2 Replies View Related

Auto Filters - Refreshing List Results

Feb 19, 2009

I have a worksheet with an auto filter to display only non-blank rows. Each row is a ledger account, with a cost. The formula in each cell sums the total of its corresponding gl in another worksheet, but also takes into account the region I am asking it to search based upon another cell. So the cost in a specific can fluctuate based upon which region I am telling the rows to lookup. So it can be blank with one region and populated with another. When I change the cell that tells the row data to look up a new region, the auto-filter does not automatically update the list of non-blank cells based upon the new region being searched, but rather the old one. So what is happening is I am left with an incorrectly auto-filtered result.

Is there a way to get the auto filter list to automatically update for the refreshed list of rows?

View 9 Replies View Related

Screen Not Refreshing While Modal UserForm Open

Mar 20, 2008

I'm launching a main form from a menu I've added. I want to make the form modal. From that form, the user can launch several other forms (that I also want to be modal). If any of the forms are modal, then the Excel sheet that is visible in the background does not refresh. If the form is moved at all it creates a dragging pattern on the screen and hides the spreadsheet. That's not so bad, except when I get to either a second or third level form and start closing them. They're still visible even though the previous level form is now on top. Most of the forms get bigger the deeper you go into the data entry process. So, as you close them and move back up the chain, it gets very confusing. If I simply make the forms modeless, it refreshes fine. I've tried using Application. ScreenUpdating = True and Me.Repaint with no success. I couldn't figure out where to put the Me.Repaint since there isn't a UserForm_Move event.

View 2 Replies View Related

Prevent Graph/Chart From Updating/Refreshing

May 3, 2008

I am dealing with plotting large amounts of data (millions of cells) across multiple series. Everytime you click, add a new series or do anything it redraws the graph taking a while. This Is there are way to prevent Excel 2003 from plotting a graph until you are done setting it up?

View 3 Replies View Related

Refreshing Actual Data Source Of Pivot Table

Dec 31, 2013

Trying to create a basic pivot table from a CSV file for our users. What I've done in the past is open my CSV and then take the option Insert > Pivot Table to create the pivot table from the open CSV/worksheet. My question here is .... how can I save this .XLS and refresh the CSV data within? Or do I need to use an external data source? I don't want to have to use any drivers. I'd love to be able to just point to a CSV or TXT file on our server and that be that.

For something else I have used Web Query strings to point to .php scripts that send back data in XML format. Is this an option?

Again, the goal is for me to create a pivot table and just have our user's open it up and have current/refreshed data within. We can run jobs overnight or on-demand that update the data source.

View 1 Replies View Related

Refreshing Pivot Table Fails To Reorder The Rows

Mar 25, 2014

I have a workbook containing many pivot tables. When I create a pivot table everything works fine. When I close the workbook, reopen it, and refresh the pivot table, the new data will appear at the bottom of the table instead of being inserted in the proper row in the table. Since the table is no longer in sequential order my VLOOKUP functions don't work properly. What do I need to do to get the already established pivot table to refresh properly?

View 6 Replies View Related

Copyrights 2005-15, All rights reserved