For Loop Taking Too Long

Feb 26, 2009

I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.

The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:

Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row

For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else

Cells(Rownum, 11).EntireRow.Delete shift:=xlUp

Rows = Rows - 1
Next Rownum
Application.ScreenUpdating = True
End Sub

View 9 Replies


Spreadsheet Taking A Long Time To Open

May 28, 2009

I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.

The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.

This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.

View 4 Replies View Related

Column Taking Way Too Long To Delete Or Replace?

Jul 21, 2014

I have a data range with just under 100,000 lines by about 60 columns. In one of the final columns I used the formula =IF(COUNTIF($A$2:A2,A2)=1,1,0) in order to set a flag the first time that each unique value in Column A appears. (I know about the remove duplicates function but the data was sorted in a certain order and I needed information specifically from the first row in which each unique value appears.) The formula works - it's not very quick, but it works.

The issue I'm encountering though is that now I want to remove that formula so it doesn't keep recalculating and every technique I use either takes forever or stalls Excel entirely. I've seen the same issue in two different spreadsheets where I used the same technique. Techniques I've tried: selecting and deleting the entire column, copying and pasting values over the formula, Selecting all the cells and pressing delete, copying and pasting an empty cell over them, Selecting and cutting and pasting the cells to another workbook. I've done these while disabling automatic recalculation and even turning off Data Filtering. I just can't understand why a simple delete operation should take so long, it shouldn't matter what is in the cell because all I want to do is delete it.

View 3 Replies View Related

VLOOKUP Performance Is Taking Very Long Time To Recalculate

Jan 11, 2010

I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

View 3 Replies View Related

Formula Taking Long Time To Get Distinct Word?

Mar 15, 2012

I get distinct word with this formula i have 30,000 rows with below formula taking lot of time with dragging to all cell


View 5 Replies View Related

Large File Taking Long Time To Close - No Interaction From User

Apr 18, 2012

I have a large file that takes a long time to close.

I would like to display a message that says " SAVING - PLEASE WAIT".

But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.

How can I do this?

View 7 Replies View Related

Macro Progress Bar For Macro Taking Long Time

Dec 2, 2008

I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.

Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.

Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.

View 8 Replies View Related

Taking Text In A Long Column And Converting It Into Corresponding Text In Another?

Sep 27, 2013

I have two columns that read:


The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?

View 2 Replies View Related

Taking End Text From Long String Of Text And Dot

Jun 14, 2013

I have a bunch of these following below which I need extract text from the first "." from the right hand side.

The amount of text and "." can vary as does the number of characters.


Ultimately i want it to display the following:

Vibrating Screen SC409
Scalping Circuit 4
Fines Stacker ST601

I have used LEFT, RIGHT, SEARCH, LEN functions in various combinations but cant get it to reference the last "." from the right.

View 3 Replies View Related

Long Calculation Loop Upon Update

Sep 2, 2009

My current task is to create a spreadsheet that will auto-hide/show rows based upon values in other cells. I am currently about halfway 40% complete with this aspect of the file. I've read over many forums, and the best what that I've seen individuals do it on other sample spreadsheets posted were a =Rand() in a random cell somewhere in the sheet, and a Private Sub worksheet_calculate(). I'm far from a VBA expert. I'm trying to learn as I go along. Here is my current VBA:

View 4 Replies View Related

Excel 2007 :: VBA - Finding Endless Loop In Long Routine?

May 14, 2014

I have a very long routine, looping through 35,000-ish rows several times to detect and delete unwanted items. Occasionally, it gets locked into an endless loop and CTRL+Break will not stop it - I have to kill Excel through the Task Manager.

How can I find out where the endless loop is so I can detect what's causingit and fix it?

View 6 Replies View Related

Taking Number In Between For Interpolation

Jun 16, 2014

I need to take number in between cells. For example, I have numbers 1, 2, 3, 4, 5, 6, 7. When I type 5 in a column, I want to have 4 and 6, above and below 5. I am doing this to ease interpolation, so that whenever I want to do interpolation, I just need to type what number, I would like to have to be interpolated.

Attached file is the example : Matic Interpolation.xlsx‎

View 5 Replies View Related

Taking Numbers Out Of A Number

Sep 5, 2007

what I have is a spread sheet of over 3000 numbers.

Now this is what the number looks like:


what I need is basically a formula or something to take out the zeros (They are not really zeros on the spreadsheet, its just an example to make it easier to display). And leave the six digits in the middle. Is this possible?

View 10 Replies View Related

Data Entry Taking An Age!

Sep 29, 2008

However data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below.

View 3 Replies View Related

Positive Cells Taking A Zero Value

Nov 4, 2008

I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero.

I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.

View 4 Replies View Related

Taking Average Of Grades?

Sep 23, 2013

I need to calculate the average of 5 grades from A,B,C,D, and E. However I want it to the average even if any number of grades (i.e. up to 5) are entered.


grade1 grade 2 grade 3 grade 4 grade 5

I have assigned a vlookup table to convert to numerical values, and got it to calculate the average, but it falls apart when any grade is missing.

I have used the iferror command on the different calculations, but when i put them to gather into one large string, again it falls apart.

View 8 Replies View Related

Taking Data From A Cell

Aug 7, 2009

Is there a fomula that would take data from a cell.

I am looking to take everything before and includuing the word LTD
For example if a cell contained
Joe Bloggs Ltd (Mr Jones) C/o USA

I would like to be able to take "Joe Bloggs LTD"

View 9 Replies View Related

Using Loops For Taking Averages

Jul 14, 2006

I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows:

startCell = ActiveCell.AddressLocal
stopCell = ActiveCell.AddressLocal

Now, clumsy programming aside, I just want to say something like this

averageCell = AVERAGE(startCell:stopCell)

View 2 Replies View Related

Taking The Last 5 Characters Of A String

Apr 11, 2007

I have a string, specifically a file path, and I want to take the last 5 characters of the string. How would I got about doing this?

View 3 Replies View Related

Taking Next Highest Number

May 31, 2007

I have a dilemma. What I want to do is: If I input a date in a cell I want the adjacent cell to take the next highest number from a list on another worksheet. My example attached

View 3 Replies View Related

Taking Team Names Into Two Cells?

May 21, 2014

How i can take just team names in A column as home and away to B-C columns ?

View 4 Replies View Related

Taking Entries From Certain Columns And Putting Them Together

Apr 4, 2014

I have a huge spreadsheet where after every 12 columns there are two "special" ones, which contain certain entries. In the first of these two, the entries from the second one are numbered in a certain way. Please see the example.

I need a formula in a separate column (yellow) which will gather all these entries from all columns, and put them in order, as I did manually in this example.

Instead of 4 "groups" of columns, in my real file I have 200 of them, with 400 rows each, which makes it more complicated..

View 2 Replies View Related

Taking Data From Website To Spreadsheet?

Jun 21, 2013

I am an excel novice trying to create a list of local churches. There are several online lists that I am taking the data from but, being a novice, I am entering the data manually. How to set up a method to bring the data into excel and order it where I want it.

I am attaching the spreadsheet.

These are the websites I am taking my data from: [URL]

View 3 Replies View Related

Matching 2 Values To 3rd And Taking Count?

Aug 29, 2013

Currently I have 3 Columns. The first column is a list of phone numbers that have confirmed and bought our product. The second column holds the phone numbers of the initial leads that we generated. And the third column is the keyword that the lead used to find our website.

What I need to do is match the phone number from Column A with the phone number of Column B then record the frequency of the keyword that was used to produce that lead.

I've attached a screen shot with the first few rows of my table (Nearly 1000 in total) as well as an example of what I imagine the result looking like.

First Few Rows Of The Table:

What I Am Looking To Do:

View 2 Replies View Related

Taking Over Spreadsheet With Macro Not Working?

Jun 3, 2014

Debug error points to:

BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")

Sub is below:

Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer


View 2 Replies View Related

Taking Row Of Spreadsheet And Putting Into Main One

Nov 29, 2013

I have a bunch of spreadsheets in a folder and I need to take one line of each spreadsheet (which is in the same place on every one) into a master spreadsheet.

I am always adding to the spreadsheets in the folder.

Is there a way to have my master spreadsheet look out for changes in the folder and when there are changes add the row into it?

View 2 Replies View Related

Taking Matching Fields And Numbering Them

Jul 15, 2009

I have a column where a lot of the fields match. What I want to do is set up a formula where all of the matching fields are numbered (1,2,3,etc). For instance:.......

I want the numbering to read the matching items and count them (not a sum but rather just a numbering/ordering column)

This is what I would want it to look like:...........

View 3 Replies View Related

Taking Sum Of Column When Some Rows Hidden?

Feb 29, 2012

I have autofiltered a column to meet a certain criteria (which hides some of the rows), then I want to put a cell with the sum at the bottom of that column. When I do this, it takes the sum of all of the rows in that column, even the rows that are hidden. Is there a way to only take the sum of the rows showing?

View 5 Replies View Related

Taking One Cell Information And Creating Two

Jul 28, 2014

I have a userform that searches a data base for an address an pulls up the corresponding information. I was wondering if there was a way to get it so that if I put 1234 5th Street when it puts the information into the data base it would separate it into two cells so 1234 would be in one section and 5th street would be in another.

View 1 Replies View Related

Taking Data From Other Spreadsheets Using A Macro

Oct 3, 2007

I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this:

Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C: est"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
Workbooks(fn).Close False
fn = Dir
End Sub
I have tried using this code, altering the Dir String to the folder the documents are in.

However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet.

To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved