Row Hiding Macro, Speeding It Up?

Aug 23, 2007

This code is pretty slow if most rows have to be hidden. I'm certain there is a faster way to do this but I'm not seeing it.

What am I doing with

For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row. I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11. As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible ...

View 9 Replies


ADVERTISEMENT

Speeding Up Macro's

Mar 13, 2008

This may be a long shot, but I've been working on a macro today which runs a few Vlookups, does some copying and pasting and some re-aligning....basically nothing too complicated. The Vlookup is looking through approx 45,000 records and returning about 200 - 300 on average, this info returned is company name, address, contact info etc etc.

This macro is taking a ludicrously long time to run, I've checked through and removed any coding that I think is unnecessary without altering the final outcome of the macro.

It still takes ages. Has anyone got any ideas why this may be, or any tips of speeding it up?

I have a brand new computer, dual core processor, 2 gigs of ram etc so it shouldn't be performance of the machine slowing it down, and I ran it with the least applications open that I can (I'm at work so can't close everything, but none of the other applications are memory hogs or anything). I'm running on Office 2003 on Windows XP.

View 9 Replies View Related

Speeding Up A Macro

May 13, 2008

I am running a rather large simmulation where I have 12 for loops with each running between 0 and 100% with variable steps and the where the sum of the 12 variables has to sum to 100% and sometimes combinations of the sums of a few of the 12 have to sum up to specific values. That part works, now I am trying to get this to run faster.

Question:
Is there a different design that might be able to run this process more efficiently?
If not, is there a way to speed up the process by letting the computer know that all these variables are between 0 and 1 (or 100%) and that I only need 3 digits after the decimal if the value is in % form?

(the only problem with the rounding or cutting off the decimals at the end that I can think of is that it will only speed up the process a bit but will also make the summing upto 100% or any other value difficult)

View 9 Replies View Related

Speeding Up Formatting Macro

Jun 28, 2007

I need help with speeding up my macro. I have been building this using alot of the recorder and the maco is just horribly sluggish. I don't know if it is possible, but it seems like it is. I need the macro to obviously work the same way. The way it has been recorded, the order of operation is important. But if any one knows how to do this better it would be a huge help. The slowest part is during the insertion of the formulas into col. J,K,&L takes like over 7min -- Macro does work perfect though.

View 9 Replies View Related

Speeding Up Macro Processing

Aug 27, 2009

Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.

One way - Press Escape and it goes about twice as fast! - Wy does it do this?

Another way - Inserting these lines into the code -

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

View 9 Replies View Related

Speeding Up An Auto-delete Macro

Jul 14, 2008

Delete rows where column A and B are duplicated.

However, this runs extremely slow. I had to stop it after about 10 min, as it only got to about row 1000 of 20,000.

Sub Delete_Dupes()
Dim rw1 As Long: rw1 = 1
Dim rwx As Long: rwx = rw1
Dim stepx As Integer
Dim co1 As Integer: co1 = 1
Dim co2 As Integer: co2 = 2
Dim bool1 As Boolean
Dim bool2 As Boolean
Dim bool As Boolean
Dim count As Integer
count = 0
Do Until Cells(rwx, co1) = ""
stepx = 1
If rwx > rw1 Then
On Error GoTo NewCrit
bool1 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co1), Range(Cells(rw1, co1), Cells(rwx - 1, co1)), 0))
bool2 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co2), Range(Cells(rw1, co2), Cells(rwx - 1, co2)), 0)).................

View 9 Replies View Related

Pasting Without Selecting And Speeding Up Macro In Background

Mar 14, 2013

1. I need to paste data that is copied from an internet window into sheet1 without selecting the cell so that I can continue working on a data in sheet5. The macro prevents me from editing sheet5 in an orderly fashion because when it goes to paste the data into sheet1 it selects the cell in sheet1 interupting what I am trying to manually do with the data in sheet5. How to paste data into a cell withou selecting the cell so that my program stops interupting what I am doing in a different sheet?

2. My macro is meant for real-time defect monitoring in a production facility. The macro opens an IE window that contains the defect occurences, copies the data, pastes it in sheet 1 then sheets2 and 3 analyze the data, closes the IE window, and then waits for a time period before repeating the process. This macro is so slow though that it is hard to work on this file while the macro is running. How to run the macro in the background and speed it up so that I can continue working on Sheet5 of the file while the macro repeats itself over and over again? Here is my code.

The variable website is determined by earlier parts of the code that direct the macro to a specific network website.
Sub Half_Hour_Data

1
'Open Internet Window and Navigate to Website
Set myIE = CreateObject("InternetExplorer.Application")
myIE.Navigate Website
myIE.Visible = True
Application.Wait Now + TimeSerial(0, 0, 10) needed to wait until the page loads

[code]....

Is there a way to speed up the macro by removing the timeserials and just telling it to continue when the task is complete. Then run the whole thing in the background so I can work on another sheet while this program runs?

View 1 Replies View Related

Speeding Up Macro Process (Graph Algorithm Stuff)

Jul 7, 2009

i'm working on floyd algorithm macro for a project,
problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet
And this is taking wayyy too long, i've been waiting for almost 2 days already..
here's the file :
http://ifile.it/6v2j39f
(You start the macro using "run" command in "input" sheet)

So is there any way to speed it up?
Or if anyone got a supercomputer, could you help me process this? :p

I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..

And here's some reference for Floyd's algorithm,
http://ifile.it/12p5zle

I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result,
and that's for 1 iteration, can't imagine how long it takes for 1043 iterations
You can do this by deleting the.

View 14 Replies View Related

Speeding Up VBA

Jul 16, 2007

I've been working on the following code and need to find a way to speed it up.

The premise is that I have a list of columns (From C2 to potentially EH and beyond) and these columns will contain the values Y, N, end or nothing. I'm looking for the quickest way to zap along the columns and delete all that have an N and to stop when end or "" is reached.

There are 2 versions as I'm not sure which one is best ...

Range("B2").Select
LastColumn = Range("A1").End(xlToRight).Offset(, 1).Column
For k = 2 To LastColumn
ActiveCell.Offset(0, 1).Select ' right 1
If ActiveCell.Value = "Y" Then Next k
End If
If ActiveCell.Value = "N" Then
ActiveCell.EntireColumn.Delete 'If employee has no sickness then delete column
ActiveCell.Offset(0, -1).Select
End If
If ActiveCell.Value = "end" Then Exit For
If ActiveCell.Value = "" Then Exit For
Next k
or a longer version

StripSickness:
Range("B2").Select
ActiveCell.Offset(0, 1).Select ' right 1
PrintView = ActiveCell.Value 'Y/N whether employee has data
If PrintView = "Y" Then GoTo StripSickness
If PrintView = "N" Then GoTo NotNeeded
If PrintView = "End" Then GoTo Complete 'last column shows "end"
If PrintView = "" Then GoTo Complete

NotNeeded:
ActiveCell.EntireColumn.Delete
ActiveCell.Offset(0, -1).Select
GoTo StripSickness 'once employee is deleted, go back to stripsickness process

Complete:
Range("a1").Select

View 9 Replies View Related

Speeding Up Vba Code

Jul 17, 2007

I've been working on cleaning up some code that copies particular information from one sheet (as the original must remain unedited) and then edits out irrelivant information.

However I've noticed that my "before" code moves far faster than my "after" code, which is odd considering that I've cleaned out a lot of excess code and the end results are the same.

I attach an example of my old fast code and my new slow code - can anyone advise where I have gone wrong ? Run the fast code first and then the slow one - you'll see the difference!

View 9 Replies View Related

Speeding Up Multistep Loop

Aug 20, 2007

first, id like to thank the board for the help on setting up an array or ranges.

ive been able to get my code to work but its painfully slow...if anyone had any recommendations on speeding it up would appreciate it...i was playing around with using formulaarray instead of writing and reading the data on each loop but cant get it to work...and i'm not really even sure thats the right way to go.

i copied the entire code below...what i was doing was taking 8 time series and looping thru each one to calculate some basic statistics...i ran the function EDFlyRateFromPrice to calculate another series of data which i then run the standardize, max and min functions on.

View 9 Replies View Related

Speeding Up Page Setup

Jun 23, 2008

This part of my macro can be improved as it runs through over 100 sheets and takes a while?

Sheets.Select
For Each sh In Worksheets
With sh.PageSetup
.Zoom = False
.LeftFooter = "&F" & Chr(10) & "&A"
.CenterFooter = "&P of &N"
.RightFooter = "&D"
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.354330708661417)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = 1
.BottomMargin = 1
.Orientation = xlPortrait
End With
Next sh

View 9 Replies View Related

Speeding Up Workbook With 12672 Sumifs

Mar 4, 2014

I am putting together a workbook which calculates a rota for 5 days that shows overall headcounts every 15mins.
It currently has 12672 cells containing SUMIFS.

But this takes a very long time to calculate.

Is there a faster / more efficient way for me to approach this task?

Can I attach the workbook so you can see what I mean?

View 1 Replies View Related

Speeding Update Process Of A Database

Jan 15, 2007

Did not want to hijack Jonny's thread about ways in which to update and streamline code to make it run faster. The update speed of my first attempt at a macro is woefully slow - 5 minutes for 2.5K records. I was wondering if some of you more knowledgeable folk could look at my code and make suggestions as to how it could be modified to run faster.

When I ctrl-break out of it I usually end up in a private sub that concatenates columns A & B in Column C (see below). When it resorts the database it must keep triggering this concatenate sub which (I think) is slowing things down.

Concatenate Private Sub:

Option Explicit

Private Sub CommandButton1_Click()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub ......................

View 9 Replies View Related

Hiding Rows Through A Macro

Dec 4, 2006

i'm having trouble hiding rows through a macro. In this case, i'd rather use a macro than use autofilter....

i would like a macro that does the followings...

1. for each row in worksheet (up until row 2000), if the value in the first cell = YES, then hide row, otherwise, leave row as is...

View 9 Replies View Related

Hiding Macro Names

May 16, 2008

How do I hide the name of Macros from viewing? Right now everything is protected, but users could see and run the macros.

View 9 Replies View Related

Hiding Cols Using A Macro

Oct 21, 2008

I use the formula below to hide rows on the current sheet using the value in cw104 on sheet "POINTS"as the upper limit & cw105 as the lower limit.

Rows(Sheets("POINTS").Range("cw104") & ":" & Sheets("POINTS").Range("cw105")).EntireRow.Hidden

i want to do the same thing with the formula below, but for columns (i want to use the value in cell cw106 on sheet "POINTS" as the upper limit & cw107 as the lower limit.

Columns("aE:aF").EntireColumn.Hidden = True

i tired it like this and it wouldnt work
Columns(Sheets("POINTS").Range("cw104") & ":" & Sheets("POINTS").Range("cw105")).EntireColumn.Hidden

can the col be numerical values and not letters?

View 9 Replies View Related

Speeding Up Auto Filter On Multiple Sheets?

May 22, 2014

I want to filter 4 worksheets, but it takes a lot of time, something like 5 seconds, I need it to be at least twice faster.

I'm using this formula: [Code]......

View 2 Replies View Related

Macro For Hiding Columns & Printing

Dec 2, 2008

Iam an accoutant and have been given a task we have a workbook we use monthly that has about 50 columns, we have to go in and hide all the columns we do not need to print so I am wanting to create one that will hide the neccessary columsn print, unhide the columns and then hide the neccessary ones and print and so and so on.

I tried to do this by recording a macro and it sent me to visual basic and now says ithas all of these errors and i dont know how to fix them i need to do this porbabaly for about 20 different hiding and printing combinations. I am going to attach the errors i am getting when i try to debug in visual basic this is just for the first combination so i guess i need to know how to do debug for the rest of the combiantions. I am also hiding the entire column i dont know if i should be or not. I am attaching the file that i am doing the hising in as well. So like for the first i need to only keep column a,b,c (always unhidden) and then g for this time, print, unhide, then again column a,b,c active, then column u print and then unhide and on and on.

View 2 Replies View Related

Hiding And Unhiding Sheets Using A Macro

Apr 27, 2012

I would like to be able to hide the tab i am in and unhide another tab at the click of the button,

I have tried recording the macro myself and it works up to a point,

I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)

View 4 Replies View Related

Offset Hiding Columns Macro In VBA

Aug 1, 2006

I am attempting to creating a Macro in excel with an offset feature but I am having trouble getting it to work correctly. I will include what I have created thus far, but this is what I would like it to do. There are 2 columns, A and B which are header columns for rows 2 through 75. Columns C through G contain values which are referenced in formulas in the corresponding cells in columns H through the end of the sheet. Ideally after every day I would like to run the macro to hide the next 6 columns which are showing. So on day 1 I want to run the macro to hide columns H through M. On Day 2 I would like the macro to offset and hide the columns N through S and so on. Each day an additional 6 columns (which is the all the data for the previous day) need to be hidden so that the current day's data is showing and can be easily read alongside the headings which flow vertically down columns A and B. If you could please help me with the proper form of this macro I would greatly appreciate it. This is the current macro I have made, I would like to add an offset command in there but am unsure which line to tie it to:

Sub HidePreviousDay()
'
' HidePreviousDay Macro
' Hide the Previous 6 Columns (the entire last day)
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Columns("H:M").Select
Range("H2").Activate
Selection.EntireColumn.Hidden = True
End Sub

View 3 Replies View Related

Recorded Macro Incorrectly Hiding Columns

Nov 18, 2013

I've recorded a macro in which I unhide certain columns, copy and paste some information then hide those columns again. The problem is that when the macro is finished, it incorrectly hides columns K to AN. I did not record that and it's not in the code so I'm lost as to why it's happening.

The macro is as follows :

Code:

Sub COPY()
'
' COPY Macro
'
'
Application.ScreenUpdating = False
Columns("K:BT").Select
Selection.EntireColumn.Hidden = False
Range("M5:N24").Select
Selection.COPY

[Code]...

Why it's hiding everything from K to AN. I've tried recording the macro several times but it's just not working, no matter the order in which I hide columns when recording it.

View 5 Replies View Related

Hiding Columns Based On Cell Value (macro)

Dec 1, 2009

I have an elaborate spreadsheet that I have made for my company. I will give a little background how I have it set up:
E6 contains a drop down menu of all products that we sell, and line items are populated based on the selection here. In columns N and O, I have a "Business Partner Costs" table that calculates a specific discount % based on the business partner.

Some of our products are sold to BPs with no discount. For these specific products, I am trying to get the BP Costs table to disappear, or hide.

Basically, I want something like: "=if(or(e6=ae2,e6=ae7,e6=ae12,e6=ae13,etc),hide columns N&O,show columns N&O)" in macro format.

View 9 Replies View Related

Hiding Columns With A Macro By Date Reference

Jan 28, 2010

I have a spreadsheet set with the days in sach column (eg 29-Jan-09, 30-Jan-09, 31 Jan-09, 1 Feb-09 etc). What I would like to do is hide all columns prior to a date which is entered into a cell/macro text box.

View 9 Replies View Related

Macro For Hiding Rows Containing Empty Cells Through Different Columns

Sep 4, 2013

I created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.

Data
Data
Functional Upset
Data
Data
Functional Upset

#
#
X
#
#

[Code] .......

Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.

Code:
Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True
K = 0
Do Until Worksheets(2).Range("I1").Offset(0, K) = ""
If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then

[Code] ......

View 6 Replies View Related

Macro Enabled Column Hiding Based On Data Validated Dropdown

Mar 26, 2014

I'm trying to write macro for hiding columns based on a drop down answer. The drop down would be in cell B7 on Sheet 1. The colums affected would be in each worksheet in the workbook. The data validation would have a drop down selection choice of 1 through 16. The columns affected range would be H through W. For example, in the drop down if 1 was selected, only column H would show. If two, H and I would show...etc. This macro should affect all worksheets in the workbook EXCEPT sheet 1.

View 2 Replies View Related

Hiding Cells That Are Not Used

Mar 10, 2014

I've created a spreadsheet that will be protect for use by others. I want to hide cells that are not used so it just leaves to print area in view and gives a simple and clear look.

I know how to hide rows, columns etc but there are always further rows and columns there. I'm sure this must be simple.

View 1 Replies View Related

Userforms Not Hiding?

May 28, 2014

I have a VBA application that seems to work fine under vista but once I start working with it in Windows 7 I notice sometimes one of my userforms do not go away when asked to hide. The code is like follows:

Application.ScreenUpdating=true
Waitingform.Hide

View 1 Replies View Related

Hiding Rows...

Jul 1, 2009

I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.

View 2 Replies View Related

Hiding Various Worksheets

Sep 7, 2009

I have a spreadsheet with 5 worksheets going, I've been trying to find code that will hide and unhide certain tabs depending on the answer to C21 of Worksheet 1.

If C21 = 1 then show sheet 3 but not 4 or 5
If C21 = 2 then show sheet 4 but not 3 or 5
If C21 = 3 then show sheet 5 but not 3 or 4.

View 5 Replies View Related







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