I have some data in a excel worksheet.
The main data are in eg row 1 to row 50; row 1 will always contain data and the last row is not fixed.
After the last record, there is always 1 empty row, followed by some data in subsequent rows.
I wish to delete the rest of the rows after the last data. eg from row 51 to row 200.
Sub Macro1()
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
End Sub
I have a ListBox in a UserForm that displays multiple branches. I can't figure out the code to delete the selected record? if the user selects "Yes" from the MsgBox?
View 6 Replies View RelatedI have an Excel worksheet full of data. I would like to search in a specific column for cell that contain "xyz". If found cell = xyz, then I want to delete the whole record or row.
Is there a formula or option to do this?
I am currently looking at each record and deleting it manually. With a worksheet of more than 2000 records, this become a huge task!
If I respond to a post and want to delete what I posted how can I do that? i can edit but don't see a way to delete what i posted...
View 9 Replies View RelatedI'm trying to create a .cvs file from an exported excel report, but before I can import it to a database I'd like to have one row per record.
I've already omitted out the headers and footers blank spaces and unnecessary columns from the excel file and I needed to just combine multiple rows into one.
It currently looks like this:
ROW#(1), ID#(1), NAME(1)
ADDRESS(1), ZIP(1)
DOB(1), SOURCE(1)
ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2)
ADDRESS(2), ZIP(2)
DOB(2), SOURCE(2)
ITEM(2), CITY(2)
and so on..
I'd like to make it like
ROW#(1), ID#(1), NAME(1), ADDRESS(1), ZIP(1), DOB(1), SOURCE(1), ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2), ADDRESS(2), ZIP(2), DOB(2), SOURCE(2), ITEM(2), CITY(2)
and so on...
I can't figure out a macro or a way to do this automatically.
I import a sheet and some records have 2 rows some 3. is there a way to add the extra row as needed based on column A?
Unfortunately my strengths do not lie in VBA for Excel. But I have a problem which needs resolving using a macro because this spreadsheet scenario happens often.
I have three columns (Record ID, Comment, Results). The Results column is added to capture concatenation of the Comments column. Using VBA, I want to concatenate Comments for each Record ID in the Results column on the row for that Record Id where the rows above have no Record Id.
Each comment should stack in order with a carriage return between them. This should happen for each row where a Record ID exists and continue for the length of the spreadsheet until it finds the last row with a Record ID and should stop after concatenating its results.
I have a text file that contains two rows of data for a single record. when I open the file up in excel, it puts each row into column A. What I need to do is put the 2nd row and combine it with the data in the first row so that i can then run the text to columns wizard and put all the data into its own column.
Here is an example of the data:
TEWAC Dresser Rand SAB 21120-14 1350 1687 3300 80 .80 50 4 2.63 EE-7592 1 2250 46 30.5 14 6
10 12 1 4 .229 .102 2 2 .0303175V/1 HL2 .13 .50 .25 1.5 394 3.000
IP23 Andalas SAB 21000-28.5 1500 1875 415 80 .80 50 4 4.22 EE-7777 80/50 Rise 1 2250 43 30.5 28.5 10
2 13 4 8 .258 .102 0 0 3175V 2 .11 .46 .25 1.5 1.500
CACA Intergen CACA 21120-33 1720 2150 6600 70 .80 50 4 4.56 R971055 50C Amb EE-7467 1 46 30.5 33 14
8 14 1 2 .229 .144 2 2 .0303175V/1 HL2 .20 .65 .30 2.0 697 2.150
What it should look like is the following:
TEWAC Dresser Rand SAB 21120-14 1350 1687 3300 80 .80 50 4 2.63 EE-7592 1 2250 46 30.5 14 6 10 12 1 4 .229 .102 2 2 .0303175V/1 HL2 .13 .50 .25 1.5 394 3.000
IP23 Andalas SAB 21000-28.5 1500 1875 415 80 .80 50 4 4.22 EE-7777 80/50 Rise 1 2250 43 30.5 28.5 10 2 13 4 8 .258 .102 0 0 3175V 2 .11 .46 .25 1.5 1.500
CACA Intergen CACA 21120-33 1720 2150 6600 70 .80 50 4 4.56 R971055 50C Amb EE-7467 1 46 30.5 33 14 8 14 1 2 .229 .144 2 2 .0303175V/1 HL2 .20 .65 .30 2.0 697 2.150
leaving me with 3 rows and one column of data.
I have multiple files with about 600 rows in each that I need to process.
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View Relatedremoving duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
Im trying to record a macro that will enter a formula in a cell each time I run the macro
Drop the Lowest2:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)
Drop Lowest1:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)
I know I can write the formula in a simpler way, but I was asked to make it like this.
The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded
This problem seemed to revolve around "digital signing" with further macro changes done from a different machine (without the proper certificate). Excel warns of the problem and then "unsigns" the project.
I've got a problem with spreadsheets that I've been working on. Now, when I tell it to record a macro, I get the macro name, shortcut assignment dialog box and then an error "Unable to record." After "Ok"ing that dialog box, the normal recording macro dialog comes up. Also, lo and behold, there will not be a macro recorded.
I've got no protected cells or sheets and it doesn't matter what security level is set. It is a "signed" macro but I can take off the signature and still have the problem. I checked for "Help - About" for deactivated modules and there was none.
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies View RelatedI am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies View RelatedNeed to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).
Sub Delete_invalid_rows()
Dim i%, j%
Dim Nr%, valid As Boolean, BYPdata As Boolean
Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant
Dim ar4 As Variant
Nr = 20
ar1 = Array(11, 14, 19, _
20, 22, 25, 26, 27, 28, 29, _
30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete
End If
Next Row_Counter
End Sub
I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.
View 2 Replies View Related