Run 13 Different Sheets Each With 50k Rows

Jun 22, 2009

It has to run on 13 different sheets each with 50k rows. Right now its taking nearly 35 minutes a sheet.

For i = 12 To 100000
line_count = line_count + 1
scanning = scanner(scanning)
current_time = timer(start_time, current_time)

x = x + 1
lngRows = ActiveSheet.UsedRange.Rows.Count

If Cells(i, 1) = "" And Cells(i, 2) = "" Then Exit For
If Not (Cells(i, 3) Like "*loop*") And Not (Cells(i, 5) Like "*Fail*") Then
Rows(i).Delete
i = i - 1
End If
If Cells(i, 3) Like "*loop*" And Cells(i + 1, 3) Like "*loop*" Then
Rows(i).Delete
i = i - 1
End If
DoEvents
Application.StatusBar = "Sheet: " & ActiveSheet.Name & " - " & current_sheet_number & "/" & sheet_count & _
" | Time: " & current_time & " | Current Row: " & i & " - " & "End Row: " & lngRows & " / " & x & " | " & _
line_count & "/" & total_lines & " | " & scanning
Next i

View 9 Replies


ADVERTISEMENT

Comparing Rows In 2 Sheets?

Jun 27, 2014

I'm trying to compare cells in 2 sheets, and if those cells are equal, I want to select the whole row that the cell is in, and copy it into a third sheet.

[Code]....

View 5 Replies View Related

Moving Rows Between Sheets

May 22, 2008

I have a dropdown list with names of different people: Bob, Jane, Joe, et cetera. I also have tabs at the bottom (sheets) for each of these people. Is it possible that when a name is selected from the dropdown box in column D, say Bob, the whole row (row 10 or whatever) is cut from main sheet and put into Bob's sheet, then the row is deleted from main sheet? If this doesn't make sense (because I stink at making sense), just let me know and I will try to clarify

View 14 Replies View Related

Matching 2 Rows In 2 Different Sheets

Apr 28, 2009

I have 2 sheets with lots of data in them. On the first sheet in the A column i have data that looks like this: 01234567 On the second sheet in the D column i have data that looks like this: 1234567 It's basically the same data except without the 0 in front. The first sheet has like 60k rows and the second sheet has around 18k rows. I need to find out what data from the second sheet is in the first one. I'm attaching an example.

View 2 Replies View Related

Copy Different Rows To Different Sheets?

May 22, 2012

Copy different rows to different sheets?

View 2 Replies View Related

Hide Rows In All Sheets?

Oct 19, 2012

I have a workbook where each sheet is password protected. I'm trying to find some code that would unprotect each sheet, and hide rows 1-12 on each sheet, and then re-protect all sheets.

View 4 Replies View Related

Delete Rows Of Other Sheets Also

Jun 23, 2007

I have a WB where the first sheet is "Introduction", second is "Master Data" wherein the name of players with some details are kept.

After that some sheets are there which are linked to "Master Data" ie say Sheet2,sheet3,Sheet5 (sheet 4 is not linked to "Master data" but appears after sheet3)

I want that if a record , say record 3 at row 5 is deleted also delete that record 3 in other linked sheets ie Sheet2,sheet3,Sheet5 The positions of the records differ from sheet to sheet ie if record 3 is at row 5 of master sheet it coulsd be on row 8 of sheet2, at row 10 of sheet3 & at row 6 of sheet 5. All the name of the person are linked to sheets with =masterdata!B5 like this.

View 9 Replies View Related

Hiding Sheets And Rows

Aug 8, 2008

Why this doesn't error but yet it doesn't work either.

Sheets("2 plans, 3 plans, 4 plans").Visible = False

Sheets("1 plan").Range("a26:a416").EntireRow.Hidden = True

View 9 Replies View Related

Compare Rows Between Sheets

Aug 22, 2006

I have 2 sheets that have 15 columns and approximately 200 rows on average, but the number of rows varies from week to week. I need to compare the information on sheet 3 to the information on sheet 1 by row. There are actually 3 worksheets total, but the 2nd worksheet is not involded in this macro. If the 2nd-14th columns are an exact match, I need to have the date from the first column and the notes from the 15th column (the formatting has to be copied too!!!) on the first sheet copied over to the matching row on the third sheet into the 1st and 15th columns, and the 16th-18th rows on the first sheet need to be copied over to the third sheet as well. After all this is done, the row on the first sheet needs to be deleted.

I need it to compare all of the rows this way, and unfortunately, I think it is going to have to loop through every row on the 1st sheet for every row it is comparing from the 3rd sheet.

I know this will take quite a while to compare, but given this takes me a couple hours on average, letting the macro run for 5 or 10 minutes is a vast improvement

View 9 Replies View Related

Insert Rows On Multiple Sheets?

Apr 2, 2008

I need to figure out a way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.

View 9 Replies View Related

Adding Rows To Sheets With 3D Referencing?

Jun 13, 2013

I have attached a file for reference. I have another file I use that is similar to the one attached, however it has 500+ rows and is still growing. The issue I am having is that in this file I want to make a "Template" worksheet. Basically I need 50ish sheets that are more or less the same. I have a column that describes a item, a items weight, a row with headers for the columns that I want to appear identical on every worksheet. I want the variables to be the count. These will change from sheet to sheet with formulas to than update the totals.

My issue I am having is that I constantly find myself needing to insert new rows for new items. In my real worksheet I have rows grouped together in a logical way so the rows cannot just always be placed in the next available row at the end of a sheet. How I can have sheet 1 be my "template" and the others take their item lists and weights from sheet 1. In the example file I created sheet 1. I than created sheet 2 and selected a cell entered "=" and selected the cell that I want it to have the same info from on sheet one. I fill handled these items down to generate a list. I than held down control and dragged a couple of new sheets. This process works, and is what I am currently doing, but what do I do when I wish to add a new rob between row 4 & 5?

View 2 Replies View Related

Merge Certain Rows, Of Different Sheets, Of The Same Workbook

Oct 16, 2008

I have a workbook of 96 sheets. I need to get the 4th row of each sheet,
and merge all, 96 of them, into one new sheet.

View 3 Replies View Related

Unhide Rows In Multiple Sheets?

Oct 12, 2011

Is it possible to unhide a row in one sheet and the same line can be un hidden automatically ini 2 other sheets within the same workbook

View 1 Replies View Related

Delete Rows On Multiple Sheets Below

Apr 22, 2007

One more short macro code I would like to have.

I would like to delete rows on all worksheets in a workbook below where the value "Total" exists in column B (same on all sheets).

View 9 Replies View Related

Copying Chunks Of Rows To Different Sheets

Sep 22, 2006

The problem sheet attached to this thread. I would like to extract all rows belonging to one person to another sheet named after the person and also their status.
For instance, I want a VB script which extracts all rows with Mark when his status is A/V to another sheet named as Mark and likewise for Steve.

View 9 Replies View Related

Joining And Inserting Rows From Two Sheets

Feb 27, 2007

I read several forum questions for data join and merge but not found a solution for the following scenario. Is possible do it ALL in one macro? I have two different xls files.

file1: sheet has the following structure
refcode price count
XXBK443 200.0 3
KKKN339 333.0 2
etc..

file2:sheet1 has the structure
refcode prod_name color height
XXBK443 prodname1 green 10
ZZZZ000 prodname2 yellow 22
KKKN339 prodname3 white 15
AAAA111 prodname4 white 30
etc..................................

View 3 Replies View Related

Compare Rows On 2 Sheets And Replace

Mar 2, 2007

I'm trying to do is compare 4 columns on 2 sheets. I would like to go down in column "E" on sheet1 and find the exact one on column "A" on sheet2. Once these are equal I would compare F, G, H, on sheet1 with B, C, D on sheet2. If the these three columns are not the same I would like to copy from sheet2 B, C, D from sheet2 to F, G, H.

The problem I have is none of the rows of information will be in sequential order as well as we may not have a match. What I'm saying is row1 on sheet1 could be row1000 on sheet2. Attached is a small example. in the example rows 6, 11, 12, 16 and 19 would be the only changes on sheet1. My example does have the rows in sequential but that would almost never be the case for me....

View 9 Replies View Related

Hide Rows Across All Sheets Where Row Sums To Zero

Jan 23, 2008

Is there a way to append this code to accomidate accross all sheets?

Sub HideRows()
ActiveSheet.Unprotect Password:="admin"
On Error Resume Next
With Range("e4:e34")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
ActiveSheet.Protect Password:="admin"
End With
End Sub

View 9 Replies View Related

Insert Rows On Multiple Sheets

Apr 2, 2008

way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.

View 9 Replies View Related

Insert Copied Rows In All But 3 Sheets

Apr 19, 2008

One of the sheets in my workbook has 5 rows (Rows 1 - 5) that I need to copy and insert at the top of all but 3 of the other worksheets. I can specify the names of the 3 worksheets that I want to avoid changing. The other worksheets will be named differently each time I use the macro.

View 2 Replies View Related

Sum Matching Rows From Multiple Sheets

Jun 14, 2008

I'm working with 2 sheets. Both have the same headings, A1 is CustomerCode, C1 is JAN, D1 is FEB and so on. The CustomerCode column doesn't match exactly between the 2 sheets, because some of the customers from Sheet1 aren't listed on Sheet2. I need a formula to put on Sheet 3 that will Sum the values in JAN, FEB, etc from the first 2 sheets for matching customers, and also bring over the totals from the customers on Sheet 1 that don't have a match on Sheet 2.

I have attached an example of how the finished product would look in my case. But also, here's this:

Sheet1 Charges

CustomerCode JAN FEB
ABC 30 25
AEF 20 15
BBB 50 30
CED 15 20

Sheet2 Misc Charges

CustomerCode JANFEB
ABC 510
BBB 3 8
CED 2 4

Need Sheet3 To look Like this:

Sheet3 Total Charges

CustomerCode JANFEB
ABC 3535
AEF 2015
BBB 5338
CED 1724

New customers are added often, so I have a SQL Query that I can put in column A of sheet3 that will pull the same CustomerCode list as sheet1, so getting the names to sheet3 shouldn't be an issue.

View 3 Replies View Related

Count & Compare Used Rows On 2 Sheets

Aug 8, 2008

I want to count the number of data rows in Sheet 1, Count the number of data rows in Sheet 2 and display a message if they are not equal. I have tried various IF(COUNTA statements but cannot get the correct syntax.
MACKE

View 2 Replies View Related

Macro For Deleting First Three Rows In All Sheets Except 1st Sheet

Apr 1, 2014

I have a excel file with more than 10 sheets and every sheet has a title on its first three rows.

I want to delete first three rows in all sheets except parent sheet .

View 4 Replies View Related

Delete Rows Based On Value In Multiple Sheets?

Apr 2, 2014

I have the following code to delete rows based on the value "No" in cloumn "L". This code should loop through all sheets and delete the corresponding rows.

[Code]....

The code works perfectly but with one little issue. It fails in the first run always with the following error message.

VBA error.png

I need to comment out the line

[Code] ....

continue it with a breakpoint to "End With", remove the comment. Then it loops all worksheets and deletes the rows.

There is no protection or similiar on any sheet.

View 1 Replies View Related

Master Sheet To Filter Rows To Other Sheets?

Aug 1, 2014

I have a work book that I am using to track volunteer hours. I often get new volunteers. Is there a code I can use to add a line item in each month from when I add a name to the master.

EX> of master sheet

A1 = Jimbo A2 = Jones
B1 = Mike B2 = Smith
C1 = Tommy C3 = Tickles
If I want to make on the master

B1 = Kyle B2 = Tanner

How do I automatically make the following sheets add Kyle Tanner in Row B Jan, Feb, March, April, May, June, July, Aug, Sept, Oct, Nov, Dec

View 1 Replies View Related

Rename Sheets By Using Dynamic Loop For Rows?

Jan 30, 2014

In column C I got some data like this:

Number of .csv
01
02
03
04

Number of .csv
05
06
07
08

Notice that there is an empty cell in between.The data starts at C12 up to C21. The data is in Sheet2.There are also 8 more sheets( Sheet3 to Sheet10). I want to rename each sheet, starting from Sheet3 according to each cell. For example the Sheet3 to be renamed to 01, Sheet4 to 02.

What I can do is something like this:

[Code] .....

And repeat this code for every block of data I got by changing everytime the i and the a. But this method is not so optimized because there are cases that the number of rows for each block is not the same and I have to change everytime the i counter. Is there any way to do 1 loop for all the sheets using maybe Worksheets.Count and another dynamic loop for the rows ? The data always start every 6 rows eg( C12, C18 etc). Also I was thinking to define an integer representing the number of rows for the loop...

View 6 Replies View Related

Macro Sort Rows In Multiple Sheets

Jan 31, 2014

Sorting.xlsx

I am trying to figure out how to make a macro that can sort some numbers from Largest to Smallest within a specific range on multiple sheets. The range is only within column D starting with cell D11: (until the data ends) on all the sheets in my workbook (the number of sheets may change with time) except for Sheets: "A", "B" and "C".

In other words I want the sort to work on all sheets except the first 3 sheets which are named Sheet A, Sheet B, and Sheet C.

I have attached a spreadsheet for an example of what I am saying.

View 5 Replies View Related

Moving Filtered Rows From Two Sheets At Same Time

Feb 15, 2014

My code below is supposed to move filtered rows in 2 sheets ("BANK ENTRIES" and "GL ENTRIES")with "Y"/"y" character on column J which is manually encoded by user. The rule is, before they can move, the total amount in column I in both sheets should match. That is the reason why rows in both sheets should be moved at the same time. I was able to figure out this rule with the use of an If Statement and a message box.

My problem is, if I run the code where there is only 1 row remaining in both sheets, "BANK ENTRIES" would insert a blank row in the third line despite my if statement

[Code] .....

The second problem is, If I run the code on "BANK ENTRIES", my first header on the "GL ENTRIES" will be deleted. If I run also the code on "GL ENTRIES", my first header on "BANK ENTRIES" sheet will be deleted. There must be something wrong on my code. I am attaching my working file for you to have a clear visibility on my problem.

Attached File : xx_xxxx_xxx_Template2_2013-11-30v3.xlsm‎

View 1 Replies View Related

Copy Over Rows To Sheets Based On Value According To Ranges

Oct 9, 2009

I am having trouble with IF, ELSE and END IF statements. In Column H I am trying to copy over rows to sheets based on value according to ranges. I am trying to use the code below but everything seems to get copied in to the first sheet "0-500". d= worksheet name.

View 6 Replies View Related

Workbook With 61 Sheets - Insert Two Rows At The Top Of Each Sheet

Jul 11, 2007

I have a workbook with 61 sheets in it.

What I need to do is insert two rows at the top of each sheet. Now I know I can manually do it, or write a macro to, that bit's easy.

However, when I insert the rows, a lot of my formulas try to pick out the wrong range.

So for example, lets say we're looking at sheet 1. When I insert 2 rows at the top this style of formula still works...

=B14/B12 (it automatically becomes =B16/B14)
=SUM(B10:B11), =B12+B14, =B12*0.25, these also update automatically.

Even a reference to ='Sheet 2'!B48*2 still updates automatically. But formula's of this kind do not...

=SUM(Sheet4:Sheet10!B5)
(regardless of inserting 2 rows, this formula does not change to =SUM(Sheet4:Sheet10!B7) )

View 3 Replies View Related







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