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 RepliesI 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.
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
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
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.
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 .............
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"............................
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
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 )
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete
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 had wanted to go through my spreadsheet and concatenate two columns (A & B)into one (A) then delete the duplicate column (B), but have found no way to do that. Now I am trying to search then insert a column prior to the other two, concatenate the data into the new column then delete the columns. I am specifically having a problem with my Range statement and can't figure out how to activate it or discern it after using the Find command.
Sub GroupGender()
Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireColumn.Insert Shift:=xlToRight
With Range("a1", Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 0) = "=RC[1] & "" "" & RC[2]"
.Offset(0, 2) = .Offset(4, 2).Value
End With
Cells.Select
Selection.Replace What:="Group Sex", Replacement:="Grp/Sx", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select.......................
I have on sheet1 a number (72 at the moment) of Form CheckBoxes.
In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)
Then, go through and Check All checkboxes
Call MyMacro
Once MyMacro is complete (Filtering & Printing)
Revert the checkboxes to their original state.
The purpose of the checkboxes:
When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden
Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.
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].....
Im trying to delete the next 5 columns in a spreadsheet whenever a specific cell value = 0 and for it to repeat to the end of the sheet.
Example:
If cell b5 = 0 then delete the next 5 columns, i've tried a couple variations, but it deletes all the 0 values in other rows.
I'm prompting the user for what two ranges they want to keep in a excel sheet and then I want to delete the rest of the columns. There may be 5 total columns and there may be 30, it will vary. The reason I want to do this is because I will then save data to CSV file and it can only have two columns of data to be passed on for other data processing.
View 5 Replies View RelatedI'm looking for the correct way of deleting columns based on if row 2 has an x in it..
I have two versions that I tried but I am pretty sure there are faster ways of doing it, I don't quite know how to delete all the columns at once.
[Code] ......
The first version doesn't work for some reason and the second column works but is a slow loop, what to do to make this faster?
I use a macro to copy some data from a .csv file. The data is copied to columns A to H (starting from row 31), the number of rows filled depends on the particular case and is not fixed. The first column gets filled with the serial numbers. the problem is that in the last row cells of columns B to H contain three dashes (---).
I have written a simple code that finds the last filled cells in column A. After having found this row, I would like to clear the cells or delete them. the below mentioned simple code does finds the last filled row but I am not able to find a command to delete or clear the cells of this row.
I have this file where i delete columns which are extra, in my real file most of the cells are formulas or links . Basically i need a macro which looks in row 4, and if it finds any zeros ( number 0 ) in the cell it deletes that whole column.
The zero is a indicator for me when i work on these files if it is needed or not. Included the file as an attachement.
How do I read in an entire row and then delete the columns that are not flagged with some value.
Example: Row 1 has a value in column A, C, D, F
I want to delete columns B, E, G+
Note: The value could be numerical or it could be a text string.
I have around 2368 rows for in each column and I have around 8 columns and what I need to do is to remove any gaps. I do not know how to attach picture here, but I can explaining it in words.
A1: 0.9
A2:
A3:
A4:
A5: -0.09
A6:
A7: 0.4
Is there a way to eliminate those gaps (A2, A3, A4, A6...) in one go?
I am looking for vba or formula can delete all columns in spreadsheet except for certain columns let's say I want to say delete all except for columns a, d, f
View 5 Replies View RelatedI'm having some problem with a part of my code that deletes entire columns. With ActiveCell as my reference, I want to delete 'Abs_Diff' Columns to the left hand side of the ActiveCell column, including the ActiveCell column. 'Abs_Diff' is a variable of integer type.
SO if ActiveCell column is "P" and 'Abs_Diff'=2, then delete Columns "O:P"
if ActiveCell column is "P" and 'Abs_Diff'=4, then delete Columns "M:P"
My code is selecting columns incorrectly, maybe due to merged cells in cols A,B,C or something..not sure.
Here is my code:
Code:
'DELETE COLUMNS
Set StartPoint = ActiveCell
For X = 1 To Abs_Diff
StartPoint.EntireColumn.Delete
Set StartPoint = ActiveCell.Offset(0, -1)
Next X
I am attempting to delete rows with columns not containing "MC". In the example below, the result should be 3 rows remaining (1,2 and 5):
Y
Z
AA
IP/DA/SV
MC/SWL/SA
MC/SH/SA
[code]...
Instead, all I get are results with Y starting in MC, in this case the last row, all the other rows are deleted. I need to get all rows that have MC in three columns (Y, Z or AA) and delete the ones that do not. In this case, rows 3 & 4.
Below is the code I have been using:
Sub Del_Rows()
Application.ScreenUpdating = False
With Range("Y1:AA1", Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*MC*"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
Seems simple enough but this newbie chump is stumped why it is not reading the other columns
-- I have one sheet with 100 columns. (Master)
-- I have a report that only needs 10 of these columns, in a certain order. (Report)
What is the most efficient way to do this? Note I am not a VBA guru.
Can I add the column headers and positions (in Master) to an array?
Then in a new sheet (Report), copy and paste the desired columns.
I tried to modify this code for my next problem: to delete entire columns where the cell in row 2 is empty, but it doesnt work. The code I use is:
Sub test2()
'This code will create a copy of the current sheet, and retain only the rows
'that have the value '1' in column B.
ActiveSheet.Copy Before:=ActiveSheet
Application. ScreenUpdating = False
On Error Resume Next
Dim ir As Long, mrows As Long, lastcell As Range
Set lastcell = Cells.SpecialCells(xlLastCell)
mrows = lastcell.Row
'Note rows are deleted from the bottom going up
For ir = mrows To 1 Step -1
If Len(Trim(Range("b" & ir).Value)) = 0 Then
Rows(ir).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub
The ideal solution would be not a second macro but one that does firstly delete the rows and then the columns.
I need code that will check each column from D:P in the " Total" row for a value of Zero. If it finds a zero then delete entire column. This will be the final procedure of a macro that does other formatting things so the number of rows will differ causing the "Total" row to be dynamic. I have the following code that will find the word total, but I don't know how to set the range to offset 1 col each time checking for zero and deleting if true. The word "Total" is merged between columns A:B. see example sheet.
Sub asdf()
Dim c As Variant
Dim Rng As Range
'search the sheet for "Total"
Set c = Cells.Find("Total", lookat:=xlWhole)
'if it is found
If Not c Is Nothing Then
Set Rng = Range(c, c.Offset(0, 3))
In a sheet where the longest column is A, I want to delete the rows between the last value in Column A and the last value in Column C.
Is there a way to delete x # of rows in columns b, c, d, g, j, m, p & s.
I regularly enter data in these rows for comparisons and then need to delete the data with out deleting the formulas in e, h, k, n, q & t.
A macro or one key then delete would be great.
What is the fastest way to delete 2 consecutive columns after skipping 2 columns?
For eg: Column A Column B Column C Column D Column E Column F
I would like to delete column A & B, skip Column C & D, and then delete Column E & F