Macro To Delete Row As Per Settings

Mar 10, 2014

I have a large file of data and the data looks like this repetitively, however, there are also useless data. But I figure out that the repeat data looks the same compare to the useless data. I need to extract the data that I only need. Is there a way to set a macro to search for a String from the beginning of the excel file, when the string is found, it will keep the 2 rows above and 56 row below it, then loop.

Here is an example of it:

A1 USELESS DATA
A2 USELESS DATA
A3 USELESS DATA
A4 USELESS DATA
A5 GOOD DATA

[Code] ....

The macro will start from A1 then go down till find STRING, then will keep 2 row above it which is A5+56 row below it which is A63.

Then continue at A64, then find STRING at A66 and do the same which is copy A64 (2 row above)+till A122 (which is 56 row below), then continue.

Since A123 to A155 does have any STRING, they will all be deleted..

View 3 Replies


ADVERTISEMENT

Works With US English Settings, Errors If Italian Settings

May 19, 2008

The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...

View 9 Replies View Related

Macro To Change Security Settings

Aug 6, 2008

Is there anyway to change the security settings in Excel 2007 using VB macros? I have tried to record the macro as I change the settings but nothings is recorded.
Basically I have two workbooks, and I want to run one of the macros within workbook 1 from workbook 2. This is straight forward, however why I try to run it, excel says that the macros are disabled in the other workbook, but I know they are not, they are all enabled (bottom option)

I was hoping to find some way of fooling Excel into thinking the macros were enabled by changing the security settings (actually keeping them the same, but making excel think that i've changed them) before I try to the macro from the other sheet. I don't know if i've explained myself very well here, I hope I have, if not let me know and i'll try to go into more detail.

View 2 Replies View Related

UserInterfaceOnly Settings (macro To Run Even When The Sheet Is Protected)

Jan 14, 2010

I tried to use the code that allows macro to run even when the sheet is protected.

View 3 Replies View Related

Code To Edit Macro Security Settings

Apr 1, 2008

I need a code to change the Macro security settings of the computer where the file is opened to enable macros.

View 9 Replies View Related

VBA - Change Calculation Settings Of Workbook To Speed Up Macro

Sep 29, 2011

I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database

I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.

I've inserted a module and produced this code;

Sub ClearContents()
Application.Calculation = xlCalculationManual
Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub

Because of all the 3 summary page formulas the code is taking a few seconds to run.

I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.

View 5 Replies View Related

Excel 2007 :: How To Adjust Protected View Settings But Protected View Tab Is Missing From Trust Centre Settings

Dec 12, 2013

Excel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.

View 3 Replies View Related

Editing Existing Macro To Preserve Option Button Settings Upon Activating Worksheet With OBs

Sep 19, 2013

I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.

I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if

The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.

Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"

[Code] .......

View 1 Replies View Related

Macro Delete Page (delete A Sheet In A Macro But When I Run The Macro)

Jun 19, 2009

I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.

Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete

View 4 Replies View Related

Restore My Settings

Mar 26, 2007

i made XL App settings in a way suits my needs...........

Is there a code to restore these settings when i reinstall the XL App ?

View 3 Replies View Related

Message Box Settings

Apr 27, 2007

Are there any settings that can be applied to a message box? ie changing the background colour or header colour etc.

View 2 Replies View Related

Change The Default Settings

Dec 16, 2008

how do i change the default settings in excel, for example when i open excel it shows numbers on both rows and columns and i want it to show letters on the colunms. i know how to change the r1c1 reference style but how do i get it to stay the way i want?

View 4 Replies View Related

VBA Causes Errors When Run In German Settings

Jul 10, 2014

I have developed a process design application in Excel using User Forms to input data. The data are placed on a sheet of a workbook with the majority of the calculations being done by cell-to-cell calculations so that users can view the formulas if they so desire. This application works well in English. We want to use the same application in our German office where it has not worked well.

In order to trouble shoot the problems, I can go to the Region and Language setting under Control Panel and set it to German (Germany). This converts the number format to a period for the thousands separator and a comma for the decimal. This seems to create a lot of problems for VBA that I have not been able to sort out.

To simplify the problem I have written a very simple program. It has one User Form with two text boxes for number input, an Enter command button and a Close command button. Sheet 1 has a command button that opens the User Form. In the User Form, I enter a number in both text boxes and click Enter. VBA code then enters the number from Text Box 1 in Cell A1 and the number from Text Box 2 in Cell A2. Cell A3 has an equation that calculates the sum of A1 and A2. All cells are formatted as General.

The workbook works as expected in English. When I set my PC to German (Germany) the application works as expected when I enter an integer in the text boxes. However, if I enter a decimal such as, in the German format, 10,5 and 5,5, the numbers entered into the spreadsheet are text (left justified) and the sum is zero. I have to enter 10.5 and 5.5 to get the numbers to enter as actual numbers and give the correct sum.

How to make VBA work with the German settings?

View 1 Replies View Related

API Call International Settings

Dec 3, 2008

I change the international setting by using the API call shown below. The settings change, but is not activated before I exit Excel and go in again.

View 7 Replies View Related

Macros And Printing Settings

May 8, 2007

Enable Macros to open it. No one else does.

Second, all the rest of us can click the print button to print the one single page that we are viewing. He says when he does this, that each time it automatically starts printing all the pages in the workbook. He wants to only print the one page.

View 9 Replies View Related

Save & Re-Use Print Settings

Feb 15, 2010

Is there a way to force a print RANGE, and add settings such as margins, and print to range to 1 page wide and 1 page tall? Can this be set with VBA? I tried to make a marcro but it crashes. This is for Excel 2002.

View 9 Replies View Related

Retrieving Color Settings

Jan 30, 2007

Is there an easy way to print out the Red Green and Blue settings (i.e. numerical values) for the current pallette in a workbook?

I can set them fine and transfer them between workbooks but that's not quite what I need.

View 3 Replies View Related

Clear Find Settings

Apr 2, 2007

In microsoft word you can clear find criteria like below.

With Selection.Find
.clearformatting
.Replacement.clearformatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

View 8 Replies View Related

Set Print Settings Per Worksheet

Mar 6, 2008

At my company we need to print our workbooks containing 3 to 24 worksheets. The first page should be printed on different paper than the other pages. From various forums I gathered that it's not possible to set the papertray using vba. Those forums suggest 3 things: 1. Windows Api functions (don't work at our company(security)), 2. SendKeys (don't work because of different printers, office versions and future-proofness) and 3.

Define printerdrivers per tray.

This last I did, so there are printerdrivers for tray3 and tray4.

With the following code I try to print the workbook:

Public Sub printSheets(strP1 As String, strP2 As String)
Dim curPrinter As String, firstPage As Worksheet, otherSheet As Worksheet, x As Integer
Set firstPage = Application. ActiveWorkbook.Sheets(1)
curPrinter = Application.ActivePrinter

Application.ActivePrinter = strP1
firstPage.PrintOut

The problem: The sheets are sent to the printers specified as strP1 and strP2, but the tray-settings from these printers are "overruled" by Excel; they are printed from the papertray that was the default of the printer when Excel was started.

Also, using the default printer dialog from Excel has the same problem; when the (windows-)default printer is Tray3 then all the pages come from there, even when printer Tray4 is selected as the printer. In Word or Acrobat etc the prints come from the right tray.

View 3 Replies View Related

How To Save The Layout And Other Preferences / Settings

Feb 25, 2009

Is there way we can save the layout and other preferences / settings (and there are a huge no of those in excel) to a file or can we copy out the file where excel saves this data ? Probably a User Interface folder or something ? This is so that when i do a fresh reinstall of MS OFFICE i dont want to spend so much time resetting the customizations ive created over time.

View 5 Replies View Related

Regional Settings, Can They Be Over-ridden Within A Project

Apr 8, 2009

I'm attempting to develop an application that will get world-wide usage, but my first tester is in the UK (Danish) and the app Kacked on a simple time format command.

Since this is intended as a self contained project, is it possible to establish those settings as part of my project?

View 13 Replies View Related

Customize The Print Settings For The Template

May 8, 2009

I currently have a template that I have made and all the info and everything is fine on it, but the problem is with printing. Every time I go to print it wants to print out 100 copies of the same thing. I tried editing the original template by printing 1 copy then saving the template, but it still wants to print 100 copies with the next try. how to customize what the print settings are for the template and make them stay permanent with the template?

View 4 Replies View Related

Copying Print Settings From One Worksheet To Another

Aug 12, 2009

I am trying to copy the print settings(including the headers and footers) for one excel worksheet to another. I have found the following hint on internet and it works fine for copying the print setting(including the head/foot) but it is also grouping the worksheets. Click on the tab of the worksheet you want to copy. Then hold down the Shift or Ctrl key and click on the tab of the worksheet where you want the settings and click on File, Page Setup and OK. How can I just copy the print setting without grouping the worksheets?

View 4 Replies View Related

Saving Default Settings In Excel

Aug 20, 2013

I am working on lots of excels, with data queries from sql and pivot tables. Is there a way to set defaults to some settings in excel (not in this specific file).

To be more specific:

1. Import data from SQL: Refresh on opening, save password
2. Formatting of number to have two digits and a comma separator
3. Pivot table: when adding to values sum instead of count, refresh when opening the file

View 1 Replies View Related

Using VBA To Save And Redo Autofiltering Settings

Aug 9, 2007

I am trying to figure out how to save a worksheet's autofiltering settings and be able to re-create those settings.

I would start off with a spreadsheet with a few Autofilters on. I would like to make a macro that will record the initial Autofilter settings, then the macro would do a bunch of stuff that would need to 'Show All'. After these actions take place, I would like to be able to return to the initial Autofilter settings. I'm not even sure which VBA command should be used for this sort of thing.

View 9 Replies View Related

Save Settings Wizard For New Computer

Jul 25, 2008

I was reinstalling office on a laptop where the hard drive had been replaced. I used the 'save settings, wizard on my desktop and put in the new settings when I reinstalled Office.

This almost worked, but not quite.

Word and PowerPoint seem to be fine but when I reopened Excel I got a number of messages saying that objects could not be loaded because they were not on the machine. Some of this was because Excel was trying to reference add-ins that needed to be reinstalled. I have done that, and there are now fewer messages - but there are still two and I can't get rid of them. One looks for 'objects' the other a single 'object'. There seems no way of finding out what the objects are in question, and what it that Excel is stumbling over. Can I do anything other than abandoning my previous settings and starting again, or else living with having to click twice every time I start excel to get rid of the messages?

I have tried uninstalling Excel and reinstalling but this doesn't help. Perhaps registry changes are the problem...and I hate the registry.

View 9 Replies View Related

Auto Chart Positioning By VBA Or Other Settings

Aug 8, 2006

i try to change the chart position (chart properties) to make it auto- move to empty cells, but anyway i fail.

say example:-
i have a excel sheet,

Worksheet 1
------------
raw data, all records.


worksheet 2
-------------
1 pivot table + a graph/chart at the same sheet.

top area of worksheet is pivot table, below is chart.
The pivot table size can be change from time to time (by increasing the records at the worksheet 1)

settings or using vba to control it? by auto move the chart position down, and not overlap with the pivot table.

View 6 Replies View Related

Settings Tab For Data Validation Doesnt Appear

Aug 10, 2006

Im trying to create a validation for cells but the settings tab under data>validation menu isnt there.

View 2 Replies View Related

Protect Worksheets & Set View Settings

Oct 18, 2006

I have a battery sizing workbook, which has various tables and active cell VBA routines, that enable the user to input a number and then select a cell from the table presented on the front worksheet. This will then display the solution including other data. I have now been requested to share the workbook and all I seem to do is lock it up and therefore stop and calculations. Therefore your assistance is required in two area's:-

1. How to protect the workbook and allow the user to select a cell on the front worksheet, without all the all the VBA routines appearing to grind to a halt! I can lock an hide all the subsequent worksheets without freezing the front worksheet.
2. I can hide all the toolbars, row and column headings under my Tools/Options which will display the worksheet neatly...... Therefore can I some how instruct the users Excel to turn these off when loading?

View 2 Replies View Related

Change Default Settings For New Workbooks

Jan 2, 2007

How can I change the default settings I get with a new Excel document?

What I want is that when I create a new Excel document, all cells should have Comic Sans MS font, Bold & size 10, with a particular horizontal and vertical alignment etc.

I am not able to do these changes using settings in Option menu.

View 6 Replies View Related







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