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 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 )
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.......................
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].....
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))
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 RelatedIn 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
I am trying to delete all the rows that have blanks in both columns "D" and "F".
VB:
With Sheets("2012")
For k = 6 To 1000
If (.Range("D:" & k) = "" & .Range("F:" & k) = "") Then
[Code]....
My production spreadsheet has thousands of unwanted columns and I am unsure as to how they appeared. I want to delete them. I have tried selecting all of them and deleting them, but nothing happens. I have tried go to > blanks, but it freezes because there are so many columns.
Also, I tried to copy paste to a new sheet, but the formulas get messed up.
For a mental picture, the spreadsheet is approx 225 rows by 25 columns.
I have two columns of data, one with an ID # and the other one with a date. Some ID# + date combinations are repeated and I need to delete the duplicates. Here is a visual:
ID# date
54 19720211
54 19720211
54 19810512
67 19590312
89 19931104
89 19931104
I need to keep only unique ID/date combinations. I would need the final list to look like this:
ID# Date
54 19720211
54 19810512
67 19590312
89 19931104
I have a spreadsheet with 4 columns.
I would like to Compare Column A with Column B, if Column A contains text or numbers anywhere in Column B, then remove the corresponding rows not just in Column B, but Column C and D as well for that row.
I'm trying to create a short script which performs the following:
Searches for a specific name in column C on my "Resource List" sheet, then When this is found, check column H to see if this is populated,Then when both criteria are met delete the entire row.
Using a link I found here [URL], I've put together the code below:
[Code] ....
The problem I have is that although the criteria are being met the row is not being deleted.
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.
Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.
These columns below are all in row A (the title row)
If column named "paper" says "white" OR "grey" AND
column named "pizza" says "mushroom" AND
column named "size" says "large" AND
column named "flavor" says "butter pecan"..
(again, only going down to the same matching cell with any data in column a)
Then delete these columns.
I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.
View 4 Replies View Related