I have a workbook which starts with data in column B. The heading of each set of data starts with Turn No. What im trying to do is to be able to delete that row and the following 2 lines under it. It will then show LTP in column B. What I need doing with that is to be cut and pasted into column A.
This is how it looks at moment....
I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet.
In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low.
The workaround:
Copy all data from data sheet, sequence top down by spend, manually delete all after 50.
My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above.
Is there a way to do this without VBA i.e. pivot tables etc
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies View RelatedI have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
View 14 Replies View RelatedI have 1000's of columns of data to go through, I just need to visually inspect the data and then delete any column containing irregular data. At the moment; I select a section of the first column of data and insert a column chart for that data. I then need to change the source of the chart data to that of the next column, to do this I click on the data within the chart and it will show the data on the worksheet that I have selected by surrounding it in a blue box, I then click on this box and drag it to the next column of data. Clicking on the blue box (which sometimes I miss, and I then have to go back to the chart to activate it again) and dragging it to the next column is getting annoying, especially as I have sooooo much data to go through. Does anyone know if there is a shortcut key for this, or how I could write a macro to do this?
View 9 Replies View RelatedI have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I have the following code for identifying the 1,3,2 patterns from sheet1 and it copy pastes those rows into sheet2. I was wondering if there is a way to modify this code to delete those patterns once it has identified them instead of pasting them into sheet2. In other word,s sheet2 needs to contain every other data except those identified patterns.
VB:
Sub test()
Dim myPtn As String, txt As String, m As Object, n As Long, LastR As Range
Dim ws1 As Worksheet, ws2 As Worksheet, myStep As Long, i As Long, t As Long
myStep = 10000 '<- change here if needed.
Set ws1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")
[Code]...
I'm a first timer here an I'm hving a problem with data. I have 3 sheets in a workbook, I want to transfer all the data to one sheet. I have 3 columns labelled Number, Name, & Sales, on each sheet some of the numbers and names are the same and I want to be able to match them up and put the sales from each sheet into a new column, so the final sheet will have 5 columns in total, if the numbers and names don"t match I just want to add those to the bottom of the matched ones.
View 10 Replies View RelatedIn the attached workbook, I have identical sets of data in column A separated by an blank cells. I need a macro for user form that searches for an item indicated in the textbox of the form and then delete the whole row in all sets of data.
It is important to keep the blank cells between the sets constant, 4 blank cells between each set, except the first set that starts with 3 blank cells.
with the following problem : i have a matrix and a list with words and i need to delete every column from the matrix which contains one of the words from my list. I have a VBA script which uses entirecolumn.delete but i need one which doesnt rely on this function. The reason i can't use entirecolumn.delete is that it messes up all of my predefined ranges. here is the script i am using :
Dim c As Range, d As Range
Worksheets("table1").Activate
For Each c In Range("matrix")
For Each d In Range("listofwords")
If c = d Then
[Code]...
i wrote the below which deletes few data from a particular column, but then its not working when the data increases, i mean yesterday i had data upto 12000 columns and today i had more than this....still macro deleted the same as it did yesterday........is there way where its the last filled row and deletes the data present in the column P.
Sub Deleteddata()
'
' Deleteddata Macro
' Macro recorded 3/30/2007 by kaliappk
'
Selection.End(xlDown).Select
Range("O65536").Select
Selection.End(xlUp).Select
Range("P14842").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("P14841").Select
Selection.End(xlUp).Select
Columns("O:O").Select
Selection.Copy
Columns("P:P").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("O1").Select
End Sub
I have data like this a column
0
1
1
2
2
3
4
5
5
5
6
7
8
9
I need to delete ALL the data that is duplicated such that the column would read
0
3
4
6
7
8
9
I am aware of the advanced filter feature that shows only unique data - but that still doesn't completely eliminate the duplicated data.
I am looking for a macro or someway that I can delete data out of a workbook that dont contain specific data. On the example shown below, if in column E it dont say one of the following Nhampton, euston, tring, bletchley, Nhamp Emd, Nhamp NJ, Nhamptn RS, Watford Jn, Bltchly MD, Bedford, Bletch CS or M keynes I need the row totally deleted.
******** ******************** ************************************************************************>Microsoft Excel - Book1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA4=
ABCDEFGH4LTP**BY**751*******5*CNDR**CRE****6On4.472*Bletchley*04+595K087Off11.47**M*Keynes05+055.242K088Hrs07:00**Euston6.296.372N139****Nhampton7.53**10DaysSX**TRAIN*GOES*FORWARD*TO*NEWST.***11From09/07/2007422(350*Nhampton*8.241W5212To07/09/2007**Euston9.25**13***PNB(*30**MINS*)***14***ASSTEuston*10.242N5715****(To*Northampton)***16****Bletchley11.12**Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What im trying to do is to be able to delete the row and shift cells up if there is data in column E.
I.E lets say e45 has data in it I then need row 45 deleted and rows shifted up.
Also is there anyway I can create a macro that can do this but password protect it.
Picture a simple spreadsheet (A1:C10). In column A, the word "delete" appears on every even numbered row (2, 4, 6, etc). Blank (no) data appears on the odd numbered rows (in Column A).
The following code was to sort A1:C10 by column A, bringing all the blanks to the top (now, rows 1 through 5) and "deletes" to the bottom (now rows 6 through 10). I then want to delete all the rows which contain the word "delete."
Selection.Find(What:="DELETE", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A185").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("B184").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
My problem is that each time the macro is run, the word delete will appear on a different row. Can anyone suggest how to fix the above code so that it simply finds the word "delete" (after the sort), then does the equivalent of [END][DOWN], [END RIGHT] (to highlight the desired ranged to be deleted, then executes the delete command.
I have a spread sheet that has a varying number of rows. I need to compare the contents of Column B for every row to a list (that could also potentally vary). If the contents are in the list then do nothing, if not then delete the row. example below.
I have attempted with a do while loop and if/elseif statements (would just change the macro is the list changes) but all rows ended up being deleted. And I would like to see if it is possible to just change the list in the spread sheet, as the users are not as computer savy.
Column B
abc
red
blue
def
ghi
red
green
List
red
blue
green
Needs to end with
Column B
red
blue
red
green
How to delete the selected data that is displayed in a combobox?
Belox is the code I have used to add the data.
Private Sub cmbnewuser_Click()
Set ws = Worksheets("Start")
' find first empty row in database
iRow = ws. Cells(Rows.Count, 27) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 27).Value = txtnewuser.Value
txtnewuser.Value = ""
End Sub
I can get the data I want to delete to show in the combobox, I just have no idea what to assign the command button to do.
'RowSource' is set to Start!AA1:AA1000, is there anyway I can make it display only the cells that contain data (as there are currently only 3 bits of data, with the potential of more)?
I need really is code and I'm sure it's only 4 -6 lines long. Ok, I need to do a simple task, don't ask why (as always, that's just a long needless explanation) but pretty much, all I need to do is delete each worksheet that has only one row in my workbook. All the other worksheets (for example if they have 2 or more rows) leave them as they may be and let them live.
View 4 Replies View RelatedI have Given Name for Data Column as Below
[Data_Table=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A$2:$A$28),COUNTA(Data!$1:$1))]
Now If I delete Raw 2 System is giving Error #Ref!
I may have to delete Raw-2 frequently
I have Work book that is quite big and I was silly and put in tables, but this ultimately slows down excel so much so that I have to wait 20-30secs while it performs a single task (PC is i7 with 4g of ram).
B/c I have been working on this Workbook for a long time it has multible sheets all interconnected and I can't go through all the sheets and redo the equations , as that wouyld just take too much time. Is there a way of leaving the data the way it is and deleting the table? This is an example below of the type of table I am talking about.
I have excel sheet it contains 200000 records.I want to delete records from 50000 to 200000.Is there any shortcut to delete data.
View 2 Replies View RelatedI have large worksheet with data. Have data in the range L121:ED490.
I need to VB macro to check cells in column L,P,T,(every 4th column) until row 490 column ED and see if the cell is blank. If any cell in column L,P,T, and so on until column ED is blank then i need to make corresponding cell in column N,R, V and so on blank. Example : If L151 is blank then need to make N151 blank. If T152 is blank the need to make V152 blank.
I have a new project that needs macro code. Your help is very much appreciated. We have a spreadsheet with duplicate accounts meaning two or three rows with the same account but different information. We want to use only one row for one account and move the new data from the same account to one row only to the right and delete the duplicates. Can someone please help me with this?I read so many post and I tried some of them but it only delete the duplicate row and not copying the new data from that row to one row only. Also, the other code I tried was retaining only the current or old data. Actually, to elaborate more, I want to get the new data in each cell of the same account in multiple row and move it in one row to the right only and delete the duplicate in that same account.
View 14 Replies View RelatedLet's say I have the following file which is imported via a macro which comes from a txt file. [Apologies it's 7mb zip file] http://www.fileden.com/files/2007/8/...mpNotFixed.zip
The way the txt file is that two rows make up one row, but SAP spits the data out into two rows. Now there are specific things that I need from these tabs. The way i can get this data is to perform sort by descending on the tabs. When you sort by a descending you get the data that i need [at the second half of the worksheet]:
Basically what I want to know when i create a macro, how can i get it do delete all the rows above a specific row. In the attached file, go to DataImport2.
I would like a macro to find the word "English" in column C and then delete all the row's above it. I cannot record this because it copy's the specific cells, which i do not need. DataImport3 is how the data comes out, All i do is sort by descending ... and i need help as stated above to delete all unnecessary data.
I am working with Excel 2003. I have a series of spreadsheets generated by my companies database program. For a special project I am trying to work on, I would be able to get my results much much quicker if I could get a macro that would do the following:
The first row is a list of headers. I need the macro to look at each cell below the header, and if any data exists in that column, I need it to delete the entire column. The results I will get will tell me quickly which headers are in certain jobs but are constantly not being used. They are trimming the database at work and this type of macro would help me tremendously.
I have a large dataset with 21 columns and 3000 rows, but to keep it simple, the data looks more or less like the first dataset in the attached excel file.
This results in 3 curves (time vs force). However, there is excess data in there, I only need data as soon as the Force becomes positive (>0) untill the Force drops below 0.02 again (I consider that the end-point of my curve). So, this dataset would look like the second set in the excel file after the macro-manipulation.
I think one of the difficulties is making sure that the macro doesn't cut of my data right in the beginning, when the curve first crosses 0.02. In that case, you get the last dataset. And this is not what I want...
I hope I explained my problem well, if not, please let me know.
So, is there anyone who knows how to do this?
By the way, is there anyway to insert small excel tables in this post? Cause I wasn't planning on having an attachment, but if I copy-paste the data in this post, than the formatting gets undone and it looks like there's no way to fix it...
I simply want to delete the last 10 rows containing date from a worksheet. No criteria other than they contain data anywhere in the row.
View 4 Replies View RelatedOK essentially this is my problem:
I have a daily spreadsheet, uou update for inputs and outputs each day and the spreadsheet gets to an overperformance figure (sorry if the spreadsheet is a lottle unclear, it is a simplified version)
On the first day of each month, the opening target is adjusted and you start again. The probelm is you dont know what the opening target is until roughly 21st of the month.
I created a macro that deletes the opening target and removes all the previous days before the start of the month.
The way i done this was:
Create a copy of "daily" tab, change b5 to equals c2 and copy and paste special as values. Then in d5-d58 and f5 - f58, vlookup values from old "daily" tab , and then copy and paste special as values. Then delete old "daily" tab and remname daily(2) as "daily"
What i have done is essentially created a new tab on 21st March, looking back to data only as far back as 1st Mar and the new opening bal and target is found. The macro can be used on the 21st of each month.
but...macro doesnt work, i have tried a few times and i think it is because i am vlookuping up data from a tab that i later delete...please help!! - thanks a mil to anybody that can give me a steer in the right direction.
Is there a way to delete this un-needed data while it is still in an array (effectively resizing the array - removing specific columns and rows) and then writing the array back to the worksheet?
Example:
(5X5) Array with the following data
I have a worksheet with that I need to delete both rows if the data in one of the columns is the same. I know how to delete the duplicate row, but I need both rows deleted..........
From the example above I would want Rows 1,2 and 10,11 and 12,13 deleted based on the data in column D