If I have columns A B C D E how do I copy A and C only (at the same time) and paste them into a new workbook? I get a popup saying it's not possible.
I don't want to copy one column at a time in case I make a mistake and item 1 in column A no longer lines up with item 1 in column C (I'll be performing this at least daily and the results will go on a website)
I have to work with a report where columns are sometimes added or removed. However, the columns I need are always there, just not always at the same place. To identify them, I have my macro search for their title (they never change) and enter the column number in a variable (iSID, iPID, iEPN, iEPT, and iOD).
I now need to take these five columns and copy them into another workbook. Usually, I would use something like Range("A:A":"D:D":etc). but now I'm a bit stuck. I wanted to use something like Range(Columns(iSID), Columns(iPID), etc), but I can't seem to be able to write that correctly or something (I get errors like "wrong number of arguments or invalid property assignment"). I guess I could copy the columns one by one using Columns(iSID), but I was wondering if there wouldn't be a more elegant way to do that.
I need an equation that will return a 1 in column F if data is available (cell is populated) in 2013 or last two years (2012 & 2013) or last three years (2011 & 2012 & 2013) or last four years or last five years. A zero counts as data.
I have data in 3 different columns (A, C, and E) to name a few. I want to average each of these columns, but if any of them include zero values, I want to exclude that from my calculation.
Example:
Column A = 10 Column B = 0 Column C = 3
Right now, my "average" formula, is showing the average as 4.33. (average a5, b5, c5)
The real average I'm looking for is 6.5. What is the best way to setup my formula?
I have a worksheet with a series of data in rows. Every 40th row is a sum of the previous forty rows.
I have a second worksheet that in a series of columns uses the 40th row sums from the other sheet. So column 1 uses row 40, column 2 uses row 80 data, etc.
Every time I update this, I create the new column using a copy and insert copied cells and then have to go to each field on the new column that uses this other worksheet to correctly update the formula for the new row. As when I do it this way, if Column 1 cell a1 formula said "worksheet2!A40, then the new column 2 cell b1 says "worksheet2!B40, where I want to make it be worksheet2!A80. I have tried highlighting a series of these corrected columns and dragging the formula over but still get a change in columns vs row from the other sheet.
I am looking to see if there is a way to do something like (in layman's expression):
I have a table where I need to make a drop down list in two consecutive columns (G and H). The list in column H will depend on the Option chosen in Column H.
Column G Compensation
Payroll
Column H COMPENSATION
Savings Box
Update Information General PAYROLL
Payment Inquiry Update Information Severance Pay
As shown above If you select Compensation in G you will have those three options in column H and the same for Payroll.
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
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.
I 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.
I am working on two worksheets. On my first worksheet I have one column with consecutive numbering starting from Cell 8.
A8 = 1 A9 = 2 A10 = 3 A11 = 4 A12 = 5
On my second worksheet I want to link to the values in the first worksheet but not in a consecutive manner. Each number will have an interval of 15 cells. As shown below:
D15 = 'link on Sheet1!A8' D30 = 'link on Sheet1!A9' D45 = 'link on Sheet1!A10' D60 = 'link on Sheet1!A11' D75 = 'link on Sheet1!A12'
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
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.
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.
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
I'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 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))