Deleteing Rows Starting With Value (text)

May 22, 2006

Using an external UNIX program that quieries a database, I export all the data found based on my query and export the report as a text file. THe text file can be up to 1 MB of raw text.

Selecting all text and pasting the data into collum 1 using PASTE SPECIAL -->(TEXT)

All Data is in Collum A

Each record returned has between 40-50 values each on a seperate row, only 10 of which I need.

I am looking for a way to find the rows that begin with the unwanted field, and then delete the entire row.

Otherwise ignore the row.

IE:
AUDFI : (data path loc here)
BILLNUM : 060606
CUTNUM : (0000)
SD : 120012
ED : 121259
CC : 123456789 MATERIAL LISTS AND ITEMS
COMMENT : Multiple lines of text here
with continuing free form data, numbers, operators,

then the next data file would repeat:

ADUFI : (data path loc here)
...
...
...
etc.,

In the above example, I want to delete the rows AUDFI, & CUTNUM and move the below items up to take the empty rows place. The BILL NUM, SD,ED, and COMMENT are filed that are always kept (as well as others). Each record has roughly the same data, each tailored to an event or object.

The number of rows of data dumped into excel can easily exceed 50000, about 200 - 1500 records in the report.

After wards, the data is dumped to a text file for easier analysis.

In BASIC my line might look like
5 START
10 if A:$="AUDFI :" then delete row, move below contents up.
20 if A:$="CUTNUM :" then delete row, move below contents up.
30 END

(I think there needs to be a loop since the vaules AUDFI and CUTNUM (as well as other unwanted fields can occurr 200 - 1500 times each.)

View 7 Replies


ADVERTISEMENT

Deleteing Some Rows But Not All

Apr 18, 2007

I have a sreadsheet. 1 Column, ColC (Starting from C4 and in this case running down to row 29,796), is full of website urls. (These are now in blue, so are active hyperlinks).

The other row, row D just have some numbers or N/A (For not available) and replates to the google page rank of the domain url.
Again, starting in Row 4 and running down to 29,796.

What I want to do is delete all duplicate urls (In Col C).
Sounds easy, but the problem is this.

as an example, say part of my list is like this;
Website URL DomainPR

View 9 Replies View Related

Deleteing Rows Within Two Define Ranges

Jul 12, 2006

I am having problems getting VBA code to work on command buttons. I have recorded a macro and pasted it into a command button - but it always results in an error message when I click the command button. I am trying to copy and paste values from one group of cells to another thus;

Sub CommandButton1_Click()
Sheets(" Graph Data").Select
Range("E45:F47").Select
Application.CutCopyMode = False
Selection.Copy
Range("E46").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End Sub

View 5 Replies View Related

Deleteing Text In A Text File

Aug 4, 2008

is there any automated way to delete text from a text file?

View 9 Replies View Related

Excel 2007 :: VBA Code To Delete Rows Based On Text Starting Content

Oct 25, 2013

Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"

EX:

05S0128
06S0112
05S2298
S25852
S36963
SA36185

I would only like to keep the last 3 lines.

View 3 Replies View Related

Unhide 2 Rows At A Time Starting With Rows 28 And 29?

Aug 20, 2014

I need to unhide 2 rows at a time starting with rows 28 and 29 IF cell N28:O28 > 0. I need this to be for rows 30 and 31 IF cell N30:O30 > 0, rows 32 and 33 IF cell N32:O32 > 0, rows 34 and 35 IF cell N34:O34 > 0, rows 36 and 37 IF cell N36:O36 > 0, rows 38 and 39 IF cell N38:O38 > 0, rows 40 and 41 IF cell N40:O40 > 0.

There is currently a formula in each of the selected cells that is why I was going for greater than zero.

View 1 Replies View Related

Display Text After A Common Starting Text?

Jan 23, 2013

I have a column of locations where all the locations start with 'SHIP TO - ' followed my the actual location which can be of varying lengths. How can I display everything after the 'SHIP TO - ' in an adjacent column. My instinct is to use a RIGHT Function, but I don't know how to get that to only display the variable length text after the 'SHIP TO - ', e.g. in another column I want this

SHIP TO - ABC COMPANY
SHIP TO - A VERY LONG LOCATION
SHIP TO - HERE

to show

ABC COMPANY
A VERY LONG LOCATION
HERE

View 6 Replies View Related

Copy Rows To Different Sheet Starting At Specific Row

Jun 19, 2013

I use the following code to copy entire rows from one sheet to another and this is pretty standard.

VB : rng.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Assuming that Sheet2 is blank, this of course copies the rows to Sheet2 starting at Cell A2 and down column A. I would like to modify the code so that I can specify at which row the copy should start. For example, I might want to start the copy at A1 instead of A2, or at A10 instead of A2.

View 5 Replies View Related

Dynamic Starting Row To Deleting Empty Rows

Mar 13, 2009

I saw this macro posted by Fengore back in 2006, and it works beautifully. Now my question: Is there a way to continue using that string but have it start from a certain row? Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

View 2 Replies View Related

Repeat The Following Rows And Formula With Column A Starting At 9150

Oct 28, 2008

I need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.

View 4 Replies View Related

Populate A Given Number Of Rows Starting At A Specific Location

Jun 2, 2014

I have a spreadsheet where I track resources needed on a project. Each resource that is selected in the resource column has a corresponding % avg and an average number of months where the resource is used. % Avg and # of Months are found in a different worksheet.

What I am looking to do is, for example: if resource A will be on the project starting in May 2015 and assigned at 30% for 7 months, I want to populate 30% starting in the colunm labeled May 2015 and copy the 30% so that a total of 7 months are populated. Also, if any of the values change (i.e: the Resource Name or the Start Month) I want to erase all the percentages that were entered in the row. How can I achieve that with a VBA code? I have formulas that retrieve the # of Months and the % Avg already.

These are the columns that I have in my spreadsheet:

Resource NameStart MonthJan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15

View 2 Replies View Related

Selecting A Range From Starting/ending Cell Rows And Columns

Feb 19, 2010

I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.

Pseudo code (how I'd like the syntax to be, but isn't):

Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With

View 6 Replies View Related

VBA To Number Rows In A Column But Starting From Specific Number

Mar 10, 2014

I would like to create a vba script when ran an input.box comes up and asks you to "enter number you want to start numbering from" and when the number is entered in the input box. e.g "7654", excel starts numbering each cell in column A from 7654 until there is an empty cell in column B.

View 7 Replies View Related

Macro To Convert Text To Rows - Inserting Rows

Mar 27, 2013

I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)

I am trying to transpose the text in these cells into rows.

VB:
Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub

The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.

Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.

View 3 Replies View Related

Macro To Only Show Rows That Contain Specific Text/Chinese Text Issue In Visual Basic ( 2007)

Jun 23, 2009

I want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.)

My text options are:

High ‚
Medium ՠҪ
Low ’á

The text arrives in the cells via a VLOOKUP

Is the chinese text a problem? i can't type it into VB.

I've been using the following macro to hide and unhide rows with a command button in the same sheet:

Private Sub CommandButton1_Click()
Toggle_Hide_Unhide
End Sub

Sub Toggle_Hide_Unhide()
Dim rngCell As Range
Dim TakeAction As Boolean

If ActiveSheet.CommandButton1.Caption = "Hide" Then
TakeAction = True
ActiveSheet.CommandButton1.Caption = "UnHide"
Else
TakeAction = False
ActiveSheet.CommandButton1.Caption = "Hide"
End If

For Each rngCell In ActiveSheet.Range("I9:I30")
With rngCell
If .Value = 2 Then .EntireRow.Hidden = TakeAction
End With
Next rngCell
End Sub

View 9 Replies View Related

Sum Starting From A Row?

Oct 26, 2011

I have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).

I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.

A B
1 150
2 200
3 250 50
4 120
5 260
6 157

View 2 Replies View Related

How To Get The Starting And End Shift

Apr 4, 2014

I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.

View 5 Replies View Related

Bar Graph Not Starting At Zero?

Jun 5, 2014

I'm working on a stacked bar graph based on the below info...

Min
Mid
Max

N1
$21.00
$30.00
$38.00

[code]....

I'm trying to adjust the starting point so that it does not start at 0.

For example...

N1 - $21.00 - $38.00
N2 - $26.00 - $45.00
N3 - $30.00 - $53.00

is there a way to do this?

View 1 Replies View Related

Starting With UserForms

Jan 24, 2008

1) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?

2) How do I get the options from the userform to the worksheet?

3) How do I put an input box on a user form?

View 9 Replies View Related

Sums Starting Over After Zero

Feb 25, 2009

I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.

View 9 Replies View Related

Hot Key Not Starting Macro

Oct 4, 2006

I have Ctrl+x to run a macro and it is not working. I have used it before fine, but made modifications this time and am having problems now. I'll just show the code for now and can give more details if the fix is not obvious.

Sub PO_Flash_Report_Detail() ....

View 3 Replies View Related

Use Text To Columns Feature To Be Text To Rows?

Jan 13, 2003

I have a cell that has a comma separated value that is 354 fields long. As such, if I use the Text To Columns feature to split the data at each column, I lose several columns (because excel cannot have that many columns).

How can I break the data at the comma, but have it list in rows instead?

View 9 Replies View Related

Selecting A Starting Line

Feb 10, 2010

I have a code that print previews a certain amount of rows that I define. This basically says print preview the first 10 lines down.

Can the code be modified so that i can select a start cell. So instead of always starting at A1 I could select say start at G1?

View 14 Replies View Related

Sum Subsequent Numbers Starting From 1

Aug 31, 2012

Is there a built-in function in Excel to sum subsequent numbers, starting from 1?

If I give for example 5 as input value to the function, it should return 1+2+3+4+5 = 15.

If I give 3 -> it should return 1+2+3 = 6 and so on.

View 2 Replies View Related

Macro Starting On Certain Criteria

Nov 16, 2006

im trying to learn abit of vba and have put some macros together however cannot figure out where to start for the following

when the workbook opens it checks sheet2 : c10 ="Y" then sheet3! is displayed. If sheet2 : c10 = "N" then opens normally.

View 9 Replies View Related

Macro's Starting From Different Cells

Nov 19, 2007

I want to build a Macro that basically moves right of the cell I am in a few cells and then copies and pastes this info into another worksheet and then prints this worksheet.

However, I can't seem to build a macro that will run from whatever cell I am in.

Everytime and try it just goes from the cell I was in when I recorded the macro.

For instance, if i build the Macro in row 1, but then want to run the marco in row 23, it keeps going back to row 1 whenever I press play.

I know there must be a way of telling it "move right 5 cells from whatever cell is highlighted when the Macro is run...etc"

View 9 Replies View Related

Displaying Numbers Starting With Zero

Apr 28, 2008

I'm trying to display payroll numbers (eg 0001234), but the zeros are not displayed. How do I get this to happen?

View 9 Replies View Related

Remove Customers Starting With A

Mar 30, 2009

I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.

View 9 Replies View Related

Starting A If And Elseif String

Apr 13, 2007

In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,

Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following
Cells(J6)*1.04)*2080/12
If False then
ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following
Cells(J6)*43.33)+((J6*1.04)*129.99
If False then
ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following
Cells(J6)*86.66)+((J6*1.04)*86.66
If False then
ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following
Cells(J6)*129.99)+((J6*1.04)*43.33
If False then
ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following
Cells(J6)*1.04)*2080/12, but put this into column P

If row 6 Column O if the month is greater then 1 then I need the formula to do the following,
=J6*2080/12

What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.

In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.

View 7 Replies View Related

Return Information On Same Row If Starting Value Is Correct

Feb 11, 2009

I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:

I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)

=IF(ISNA(MATCH("N06*";Sheet1!AI:AI;0));"error";INDIRECT(ADDRESS(MATCH("N06*";Sheet1!AI:AI;0)*1;1;1;T RUE;"Sheet1")))

I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.

To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.

View 3 Replies View Related







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