Modify Macro That Deletes Rows

Aug 20, 2008

i have this macro made by another member here that looks for "T" in column 10 and deletes entire row if in that row has a "T". I need it modified to also delete if it contains "FT" and also erase if cells is blank.

Sub Macro8()
Sheets("FORMERS").Select
'deletes entire rows based on a text on one row (this case errases all that have a "T" in column 10 (column J)

Dim DeleteValue1 As String
Dim rng1 As Range
Dim calcmode1 As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With...........................................

View 9 Replies


ADVERTISEMENT

Writing A Macro That Deletes Blank Rows

Dec 11, 2008

I need help writing a macro. I only have experience recording them, not writing them in VBA.

I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.

View 8 Replies View Related

Speed Up Macro Code That Deletes Rows

Sep 13, 2006

I am trying to parse and remove unwanted rows from a very large text file using At the moment, the application runs rather slow and was wondering if the experts could give some pointers on how to make the code more efficient while still keeping it simple so others may be able to modify later. I am keeping the ScreenUpdating True as the alternative false will just show Excel as Not Responding to the user until the VB is finished.

Sub deleteReplaceRows()
Application.ScreenUpdating = True
Dim DeletedRows As Integer
Dim lastRow As Long
Dim Arr(7)
Arr(1) = "<PUZZLE>"
Arr(2) = "<%"
Arr(3) = "%>"
Arr(4) = "Response."
Arr(5) = "</PUZZLE>"
Arr(6) = "<HINT>"
Arr(7) = "<MESSAGE>"

For i = 1 To 7
Do
Set rng = Columns(1).Find(Arr(i))
If rng Is Nothing Then Exit Do
rng.EntireRow.Delete
DeletedRows = DeletedRows + 1...................

View 2 Replies View Related

Put Together A Macro For A Command Button That Deletes Rows With Certain Text In A Document

Feb 4, 2009

how to put together a macro for a command button that deletes rows with certain text in a docyment - which worked fine a week or so ago - but now the macro will not work.

I made changes to the document - but made sure i also made changes to the code.

View 2 Replies View Related

Modify Macro In Excel / Transpose - Delete Contents - Skip X Rows - Repeat?

Mar 7, 2013

I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.

Basics for Macro Requirement:

1. Find the blank cell in row A
2. Skip the next cell/row (this is the company)
3. Select all the cells beneath the company cell, until it reaches the blank cell underneath
4. Copy, transpose these cells next to the Company cell (transpose in column B)
5. Delete the contents in row A that were just transposed
6. Find the next cell with data (company)
7. Repeat Steps 2-6

My recorded Macro:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select

[code]....

View 5 Replies View Related

Macro That Deletes Certain Results

Jun 30, 2008

I am making a macro that deletes #N/A's, but i want to add a few things to it
that should be deleted:

the phrases "total board", "total metal" and "Item", and any non-text (i.e. any
number) that appears.

this is what i have so far:

View 9 Replies View Related

Macro That Deletes Row Before And After Certain Phrase

Nov 3, 2008

I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.

So for example,

(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below

If the macro finds the text in column 2, it would delete 1, 2 and 3.

I need to have a second macro that performs a very similar function.
it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.

View 3 Replies View Related

VBA Code Which Deletes A Macro

Aug 11, 2006

I’ve got a macro that automatically starts when the workbook is open. The macro finishes with

Application.Dialogs(xlDialogSaveAs).Show

The document will be always saved with a new name. As I just need the macro once when the workbook is created I’m looking for a code that deletes the macro before the workbook is saved. I tried to record a macro while I was deleting another macro, but apparently it is not working (the recorded macro is empty).
Does anyone know if there exists a code to delete a macro?

View 5 Replies View Related

Speed Up Macro: Deletes All Duplicates

Jun 6, 2007

It takes all numbers in column 4 starting with Row 15 and deletes all duplicates. It then shows the number of times the number was duplicated and puts this number in column 3. MY PROBLEM: The macro searches each line and takes FOREVER! I have data with thousands of lines. I already tried the screenupdating method which really doesn't help that much. Is there possibly a better code for doing this?

Sub Factor()
Dim sID As String
Dim sOldID As String
Dim lLastRow As Long
Dim lrow As Long
Dim lcount As Long
Dim lLoop As Long
lLastRow = ActiveSheet. Cells(Rows.Count, 1).End(xlUp).Row
lrow = 15
sID = ActiveSheet.Cells(lrow, 4).Value
sOldID = "ActiveSheet.Cells(4, 15).Value"
lcount = 1
lLoop = 1
Do While Len(sID) <> 0
If sID <> sOldID Then
If lLoop = 1 Then.................................

View 5 Replies View Related

Macro Stops Every Time It Deletes A Sheet

Oct 15, 2008

I have a macro that that stops every time I’m trying to delete the sheet. I get this message. “Data may exist in the sheet(s) selected for deletion. To permanently delete the data, prese Delete.” and I have to manually click “delete” to continue running the macro. How can I avoid this stop so that macro runs thru it without stops?

View 2 Replies View Related

Macro That Deletes All Cells In Column D Of All Worksheets

May 22, 2007

I need a Macro that deletes all cells in column D of all worksheets in a book that are equal to the word "Timeout" and to shift the cells to the right of the "Timeout" cells into the deleted cells but leaving all other rows where the word "Timeout" is not present.

So in the case of Rows 2, 4, 6 and 8 containg the word "Timeout" in column D, I would like cells D2, D4, D6 and D8 deleted and the remaining cells shifted to the left but cells D1, D3, D5 and D7 left as they are.

Here is an example line of data:

16:02:30LEXKRunningprocedure:125sth
16:32:30LEXKRunningTimeoutprocedure:X125sth
17:20:54LSXPRunningprocedure:oxford
17:56:48LSXPRunningprocedure:xoxford
18:48:24LEXKRunningprocedure:125sth
19:18:24LEXKRunningTimeoutprocedure:X125sth

View 9 Replies View Related

Prevent Formula #REF! When VBA Macro Deletes Columns

Aug 5, 2009

I'm using the following code to delete columns:

Dim rng As Range
Dim i As Integer, counter As Integer

Set rng = Range("1:1")

i = 1

For counter = 1 To rng.Columns.Count

'If cell i in the range contains an "x",
'delete the column
If rng.Cells(i) = "x" Then
rng.Cells(i).EntireColumn.Delete
Else
i = i + 1
End If

Next

My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?

View 4 Replies View Related

Modify And Enhance Recorded Code - Delete Rows Based On Value

Aug 22, 2012

I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver. I recorded code below and it is done in seconds. make it dynamic, VBA, since number of rows is every month larger. Basicly, I need code which delete rows based on Column B, where walue is "R"

Code:
Sub DelRowsBasedOnOneCriteria()
'
' DelRowsBasedOnOneCriteria Macro

[Code]....

View 4 Replies View Related

Macro That Deletes Sheet With Control & Shows UserForm Causes UserForm To Disappear

Jun 15, 2009

This is weird - if you delete a sheet that contained a control then

a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End
b. public variables lose their value

These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?

View 9 Replies View Related

Run Two Macro When I Modify One Value In A Cell

Feb 9, 2009

I need to run two macro when I modify a value in a cell

Here is the code for the macros:

View 14 Replies View Related

Macro To Modify Several Worksheets

Jun 21, 2013

Due to the lack of time and not being able to make it work otherwise this is what I have right now:

If Range("g59") = 1 Then

Sheet1.Activate
Application.Run "Edit_view"
Sheet3.Activate
Application.Run "Edit_view"

[Code] ...

There was a formula to enter sheets names in a cell range (ie: A1 would have value SHEET1, A2 SHEET2, etc) and use a FOR loop to cycle through the named sheets.

View 1 Replies View Related

Modify Macro To Add Hyperlink To Worksheets

Oct 4, 2012

I have this macro that I use for taking information based column B grouping it together and putting in a separate worksheet.

What I would like to do is in the master sheet (sheet1) to create a hyperlink for those listed in column B so I can click on it and it takes me right to that worksheet.

Sub exporttoWS()
Application.ScreenUpdating = False
Dim i As Range, LR As Long, ws As Worksheet, wb As Workbook, C As Range
Sheets("Sheet1").Select
Range("A1").Select
'looking at the full length of the file
LR = Range("A" & Rows.Count).End(xlUp).Row
'sheet needs to be named sheet1, all data should begin on row 3

[code].......

View 3 Replies View Related

Modify Macro To Use File That Changes Name-monthly

Apr 20, 2007

I have this macro that I am using but want to automate the file it uses. This file has the last 5 text/digits change each month, e.g.

Internet_GB_Report_Feb07
Internet_GB_Report_Mar07

Clearly the next file would be Apr07. I would like the macro to identify this automatically and use the correct file without having to go into the macro every month and change the last 5 text/digits. If you have any ideas I would be very grateful to hear from you. The Macro is below:

[/code]strPath = "c:Internet"
strFilename = "INTERNET_GB_FEB07"
strThisWkb = ActiveWorkbook.Name
intReports = Workbooks(strThisWkb).Sheets("Lookups").Cells(1, 2)
Code:

View 9 Replies View Related

Macro - Merge & Modify Formula

Dec 17, 2009

I use the merge button a lot for a group of cells in a column. However then I have to edit the formula in the merged cells.

I'm hoping there is a macro that can be made to bring these together.

Here's an example of what I do:

select range C2:C4. Cell C2 contains the formula =sum(B2)
merge these cells.

edit formula in new merged cell to be =sum(B2:B4)

Can I group this process into one macro?

View 9 Replies View Related

Modify Macro To Give Myfilename Ddmmyy_hhmmss

Dec 9, 2008

Is it possible to modify this macro below to concatenate what is in cell B4 (myfilename), with B5 ( =now() )and retain the correct time date format that I have specified (I formatted the cell so that there were no / or : in the file name i.e.ddmmyy_hhmmss)?

When I try this the file name always reverts to the microsoft date "serial number:
I would like myfilename 09122008_1851

but instead get myfilename 39792

(or would it be better to include the date and time in the macro rather than in the cell?)

View 6 Replies View Related

To Modify User Defined Function Macro

May 8, 2007

The below macro(found in VBA and MACROS for Excel) works well but I need a few modifications

Option Explicit

Function SortConcat(Rng As Range) As Variant
'Rng —The range of data to be sorted and concatenated.
Dim MySum As String, arr1() As String
Dim j As Integer, i As Integer
Dim cl As Range
Dim concat As Variant
On Error GoTo FuncFail:
'initialize output
SortConcat = 0#
'avoid user issues
If Rng.Count = 0 Then Exit Function
'get range into variant variable holding array
ReDim arr1(1 To Rng.Count)
'fill array
i = 1

1)My array is 128 cells wide(AT3:FQ3) and i get a type mismatch--seems to work well for 36 cells

2) These cells contain dates and numbes---all i want to sort and/or display is the numbers
Note: some numbers are in parenthesis and other are either single or double digits. Example: numbers are (8/8),8,10,(HG6),4,6,etc. And Dates are 04/06 format (no year, and no parenthesis)

3) All empty cells in the array show up in the above SortConcat function as zero, and are un-necessary

4) The final output should be no more than 6 concatenated numbers, so column width is not an issue, and the bubble sort is really not necessary but would be a nice feature

View 9 Replies View Related

Protect Sheet But Allow Macro Code To Modify

May 24, 2008

I am trying to design a tool which dynamically results in different cells depending on users inputs. Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells. Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?

View 4 Replies View Related

Excel Macro To Modify Hyperlink To Copy And Paste

Feb 15, 2014

I have a workbook that has 2 worksheets. Sheet1 has a data table that is filled from an SQL database. I would like the first column of the data table to have each cell be a hyperlink. The destination of the hyperlink is Sheet2, cell A2. I would like to modify the hyperlink, so that when the user clicks on the hyperlink, the text-display of the hyperlink is copied into sheet2 cell A2. Initially, I thought this was simple. I added the event code shown below:

[Code] ........

This worked, however there was a problem. I manually added the hyperlink to all cells in column1 of sheet1. However, this made the TextToDisplay property the value of the first cell for all hyperlinks. So, solution to add hyperlinks to all cells in the first column, with TextToDisplay property set for each individual cell. What is the best way to do this with the fact that the number of rows in column1 will vary as the size of the SQL query changes?

View 1 Replies View Related

Modify Macro To Paste Results To Last Empty Cell

Feb 21, 2014

I have this macro and at the present time, can only compare two worksheets. One of my worksheets contains column headers with data, the other worksheet just contains column headers. The macro will match the data against the column header and paste below. I'm doing it this way because I have several tables I export from a MySQL database, but some of them have more fields than others so I was trying to get all of the data to line up in under the correct column header from each table. I need editing the macro to allow for

1) More than one worksheet to be analyzed at a time
2) If the first request is not possible, I would like for the macro to paste results at the next empty cell. As of now, it will only copy one at a time (which requires me to house the data in another worksheet everytime I run the macro).

Below is the code:

Dim ws1 As Worksheet, ws2 As Worksheet
Dim HeaderCells As Range, Hdr As Range, hdrFIND As Range

Application.ScreenUpdating = False 'speed things up
Set ws1 = ThisWorkbook.Worksheets("HEADERS") 'Define name of worksheets
Set ws2 = ThisWorkbook.Worksheets("B")

[Code] .......

View 1 Replies View Related

Modify Macro To Check And Turn Off Sheet Protection.

Dec 14, 2009

I am using the macro below to import every spreadsheet into a folder. I would like to modify it to check the sheets it is importing for sheet protection. If the sheet protection is turned on, I would like it to turn it off. The password for the sheet protection will be "PIR".

View 3 Replies View Related

Deletes A Row If It Finds A Specified Value In A Specified Column

Dec 23, 2009

deletes a row if it finds a specified value in a specified column (in this instance, "NB" in column E). However, it is very slow and some end users are complaining about the amount of time it takes to run. Here's what I'm using at the moment:

View 5 Replies View Related

VBA Code That Deletes All Data Except For Highest Value

Mar 27, 2014

What each piece of this code means and what it is doing? The more details the better

[Code] .....

View 1 Replies View Related

Removing Dashes Deletes Everything After First Dash

Mar 25, 2013

I have entries that look like this - 300-7188258-1

300-8983980-5

I need to remove the dashes. I find and replace all - with nothing. that has worked for YEARS. Seems simple to me.

Today, when I do it, Excel leaves the "300" and removes everything else. I even did a CLEAN function and it still does it.

View 3 Replies View Related

File Unstable If It Gets To Big: Only Deletes The First Entry

Jun 28, 2006

The file that I am currently working on is getting really big. It is currently 101MB and we aren't done yet. In contains a lot of Macros and also a lot of ordinary excel formulas. I have recently noticed that excel sort of gets unstable with this file. For instance if I select a very large area eg A200:IK19000 and delete it, it only deletes the first entry (A200). What is going on? Is this a typical excel thing? What can I do to fix it or avoid it in the future?

View 2 Replies View Related

Loop Deletes Previous Entries From Array

Oct 17, 2006

I think the loop is deleting my previous entries and only putting the last results in.

For assortedrowindex = 3 To 400
targetdate = Date
Do While Month(targetdate) = Month(Date)
Redim Preserve arrTransactions(assortedrowindex - 2)
arrTransactions(assortedrowindex - 2).CUSIP = Cells(assortedrowindex, 12)
arrTransactions(assortedrowindex - 2).OrderDate = Cells(assortedrowindex, 9)
arrTransactions(assortedrowindex - 2).BuyCurncy = Cells(assortedrowindex, 2)
arrTransactions(assortedrowindex - 2).SelCuurncy = Cells(assortedrowindex, 4)
arrTransactions(assortedrowindex - 2).Fund = Cells(assortedrowindex, 7)
arrTransactions(assortedrowindex - 2).SettleDate = Cells(assortedrowindex, 10)
arrTransactions(assortedrowindex - 2).BuyUnits = Cells(assortedrowindex, 15)
arrTransactions(assortedrowindex - 2).FxRate = Cells(assortedrowindex, 16)
If targetdate < arrTransactions(assortedrowindex - 2).SettleDate Then
' Sheets("Sheet2").Activate...............................

View 4 Replies View Related







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