Macro To Delete Columns If They Contain Data
Dec 16, 2008
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.
View 12 Replies
ADVERTISEMENT
Sep 7, 2007
I have written a complex macro but i would like to delete some of the columns from my spreadsheet using the macro? I can highlight the column but then dont know what next to write to get the column deleted.
View 4 Replies
View Related
Dec 19, 2012
For now, I have data in Column A to E. I have the first row (A1 to E1) contaning different numbers. I need a macro that delete all collumns that cells (in A1 to E1) contain number 1. Next month, I may have 10 columns (A to J) and I want to run the same macro that delete all columns that A1-J1 containing value of 1. The number of columns changes every month, so I want a macro that work to the last column without the need to specifying that column range.
I was using this, but did not work propery (since It just deletet every other column & have to run it a few times to complete & need to specify column range):
Range(A1:E1).Select For each cell in selection If cell.Value>1 Then
Cell.EntireColumn.Delete End If Next cell
View 2 Replies
View Related
Aug 6, 2012
Code:
Range("A1,A:A,C:C,E:E,D:D,G:G,H:H,I:I,J:J").Select
Selection.Delete Shift:=xlToLeft
When I wrte a macro as above, i get error "the command cannot be used with selections that contain rows or columns and also other cells. Try selecting only entire rows, columns or just group of cells".
Actually i want delete all columns except B:B and F:F
View 2 Replies
View Related
Dec 27, 2006
I have a CSV file with the following columns:
column1, Column2, Column3
A, B, "C,D"
I need to write a macro to :
1. Delete column2
2. Save the CSV file. At the time of saving I need to supress all default messages.
View 9 Replies
View Related
Sep 22, 2004
I am developing some code to extract selected worksheets to a new workbook, and provide the data in a "standalone" form where certain formulas have been converted to values etc. As part of this project, hidden columns are made visible, then data is pasted as values on a row by row basis, then the previously hidden columns are deleted. Here is the code that unhides the columns and identifies those columns as ones that it needs to delete later on:
'Expose hidden columns and keep a list
For i = 1 To 200
Range("a1").Offset(0, i - 1).Select
If ActiveCell.EntireColumn.Hidden = True Then
colnum = ActiveCell.Column
ActiveCell.EntireColumn.Hidden = False
If HideCOL <> "" Then
HideCOL = HideCOL & "," & colnum
Else
HideCOL = colnum
End If
End If
Next
The macro then goes about its business doing this and that, and at the end comes back to delete those offending columns whose numbers have been stored in the variable "HideCOL".
'Delete columns that were hidden
If HideCOL <> "" Then
Dim colArray As Variant .............
View 3 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
May 28, 2009
I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
View 2 Replies
View Related
Sep 21, 2012
two things on the attached sheet.
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
View 6 Replies
View Related
Mar 18, 2014
I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.
View 1 Replies
View Related
Mar 14, 2008
I'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?
View 3 Replies
View Related
Apr 21, 2014
Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.
View 3 Replies
View Related
Feb 10, 2009
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...
View 6 Replies
View Related
Nov 18, 2005
In the sample below I need a macro that will delete the entire rows
where the data is repeated in a given column---that is, that it would delete
the repeat ones, leaving one of them. In this example, in the first set, all
that should be left are two rows which contain ME22N and MR8M in column E.
ME22N is repeated two more times and those rows need to be deleted.
If I could get a macro that would ask for the column to operate in, that would be
great for other spreadsheets where it would not necessarily be column E.
ABC DE
MACLEOLLynn MacLeodP0040104ME22N
MACLEOLLynn MacLeodP0040104ME22N
MACLEOLLynn MacLeodP0040104ME22N
MACLEOLLynn MacLeodP0040104MR8M
MACLEOLLynn MacLeodP0050006MB0A
MACLEOLLynn MacLeodP0050006MB0A
MACLEOLLynn MacLeodP0050006MB0A
MACLEOLLynn MacLeodP0050006ME21N
MACLEOLLynn MacLeodP0050006ME21N
MACLEOLLynn MacLeodP0050006ME21N.............
View 12 Replies
View Related
May 6, 2003
I need a macro to delete rows that contain specific data in column A. For example, in the attached spreadsheet below,I need the macro to look for data in rows A1 - A7 (highlighted in yellow) and then delete the rows. The same data occurrs several times in the spreadsheet. The macro will need to delete all rows where this data occurs in column A.
Microsoft Excel - Book2___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)boutD9=
ABCDEFGHI1
[Code] .......
View 9 Replies
View Related
Mar 31, 2008
I am looking for a macro to do the following:
Once data is copied to a tab labeled "data", it will sort on column H and delete all rows of data that do not have a AI or an RI code on it. Is this difficult to do?
View 9 Replies
View Related
Oct 27, 2008
I have an Excel Workbook with a Paid sheet and an Outstanding sheet both sheets have a File Number column.
I would like to create a macro that looks through the Outstanding sheet and if the File Number from the Outstanding sheet is also in the Paid sheet can the entire row be deleted from the Outstanding sheet.
View 9 Replies
View Related
Jan 20, 2010
i am in need of a macro to delete the rows with data only which then leave the sheets cleared and ready to use the next day. if i create a macro to clear say fifty rows of data and then tomorrow there could be 75 rows of data and the macro will fail clearing the sheet. if there is a solution can i just say i can create macros but never stepped into them in visual basic to change them, i guess your thinking what do i do if the macro doesn't work. and the answer is indeed yes i delete it and start it from the begining again and again till i get it right.
View 9 Replies
View Related
Jan 15, 2007
after importing data (data >> get external data >> import text file) and running a macro that contains
Cells(i, 5).EntireRow.Delete Shift:=xlShiftUp
i cannot insert any data into the next column below. why is this so? is it because of the excel memory that remembers that a macro has been previously ran in that cell before? example i import text file into B1 then the data will be imported into different cells using delimiters. the data will occupy from row 1 to 22. After i run the macro which combines fields with similar data the data will occupy row 1 to 15. then when i try to import data into B16. the "import text file" option will be grayed out. why is this so? how do i solve the problem?
View 4 Replies
View Related
Jun 24, 2009
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
View 4 Replies
View Related
Jul 15, 2009
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 )
View 3 Replies
View Related
Nov 18, 2009
I receive spreadsheets each month in the same format. It is effectively 6 rows of titles, 7 rows of weekly data, 2 rows of weekly summary, 3 blank rows then it repeats itself with the 6 rows of titles etc... down the page for 8 or so weeks.
I wish to create a (looped) macro that will leave the top set of titles and the 7 rows of weekly data (Mon to Sun) then delete the next 11 rows and so on and so forth to the end. I will then be left with the intro to the dataset followed by a whole months worth of data in continuous format as opposed to weekly summarised sections. But I am stuck as my macro knowledge is basic and I can only record macro's not program VBA.
I can create a macro and manually delete all entries for one spreadsheet but would rather have a code to do this than can run (x) amount of times in case the size of data changes. When it deletes section by section, the cells move up and the row numbers change.
View 3 Replies
View Related
Mar 31, 2009
I am trying to write a macro to delete all connections from a very large excel file
I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)
The issue is the 1000+ connections on this file make it next to impossible to operate
I cannot recreate the file
macro to delete all connections in all sheets in a given work book.
[URL]
Macro used :
Sub Dump()
Dim myURL
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text
Sheets("Sheet2").Select
Range("A65536").Select
[code].....
View 3 Replies
View Related
Dec 6, 2006
I 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).
View 2 Replies
View Related
Mar 8, 2014
I have data in sheet 1 like below
Custid loc city
123 us newyork
124 uk wales
876 in mumbai
Sheet 2
custid newcustomerid
123 756
124 394
Outputsheet:
Custid loc city
756 us newyork
394 uk wales
876 in mumbai
So it will check sheet1 custid with sheet2 custid. If both matches it will replace custid with newcustid.if cust id not exists it will load sheet1 recprds as usual.
View 1 Replies
View Related
Aug 20, 2013
I need a Macro that will search all of the cells for specific text, and delete all cells containing the text. I prefer to make a list of the specific texts and run macro to find all of them and delete all cells containing the text. I need to be able to add some more text into the list in future.
View 9 Replies
View Related
Jan 10, 2012
What i have is a workbook that can have multiple sheets. Sheet 2,3,4 (if there are) are a continuation of sheet 1. First 2 rows of sheet 1 are headers and the last row of the last sheet (can be 2,3, 4....) is a footer. There are 14 columns (A-N) in each sheet. Col "N" has set status and col I has set names.
What i am looking for is a macro that can perform the following actions in the the set order:
1) deletes all rows where status in col N is "closed"
2) delete all rows where name in Col I is "Many"
3) if there are multiple sheets copy all data in 1 sheet once above 2 are deleted
4) I want to update cell A2, C2, I2, M2 & N2 with Hostel, Direction,Name,Flight & Status respectively
5) Create a pivot table with all data (Col A-N & Row 2 to last) in a new sheet and call it Summary - Pivot
I can then manually select how the pivot will look
Currently i am going through all sheets manually and filtering data. Using excel 2007
View 9 Replies
View Related
Jul 25, 2008
I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3 Transactions for CUSTOMER ID: 0000CUST7 Sales 4 5 6Product summary for Vehicle ID 00001080 7 8Product Description Transactions Quantity9 101 Unleaded 3 57.60 GL11 12Hose summary for Vehicle ID 00001080 13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get the data into a more convenient format for analysis. I need a macro that will:
1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).
2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded 3 57.60 GL111081 Unleaded 6 84.70 GL121122 Unleaded 5 47.00 GL131182 Unleaded 8 95.80 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Dec 20, 2008
I have a spreadsheet that allows room for 35 students per period, but instead of having the teacher manually go in and delete the extra data and hide the rows, I want to create a macro that will do so.
In cell B4 the teacher will enter how many students are in their first period class. (cell C4 for second period, cell D4 for third etc...) I want the entering of the number to automatically hide the superfluous rows and delete the data in the second column for those rows. I don't want the rows to be completely deleted because another year they may have more students and need those rows back.
I have attached one of the workbooks that I need to put this macro into. I have created room for 35 students in a given period. So if they have 23 students entered into B4 (period one) I would need rows 30-41 to be hidden, and I would need the formulas in B30:B41, E30:E41, H30:H31, K30:K31 etc....deleted.
Basically the point of the formulas is the teacher will enter the total points possible on that given assignment in cell B6, E6, H6 etc....and it autofills that score down, so the teacher only enters those that missed points instead of entering in the missed ones and the 100 percent ones.
I would need those formulas to be deleted because if the assignment was out of 10 then cell B30-B41 will give students a 10, and then the class average will be computing those scores,but those students don't exist.
But if I can't get the macro to auto delete the formulas, I will just not have those formulas in there, and the teacher can enter all scores.
View 9 Replies
View Related
Nov 7, 2012
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.
View 1 Replies
View Related
