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 RepliesI have the following code, to find the 1st cell in column CG that contains a value of more than -200. I wish to delete the entire row that this resides on, and all rows below.
Range("CG2").Select
Do While Not Selection.Value > -200
Selection.Offset(1, 0).Select
Loop
I'm trying to write a macro that if a 1 appears in column N (1 may appear more than once & the sheet continues forever) then to hide that particular row.
I will also then have a seperate macro to delete the entire rows where 1 appears.
My data is in one column as follows:
ABC
(BLANK)DEF (BLANK)(BLANK)
What I want to do is to delete the blank cells and count how many not-empty cells.
The following sub works fine if I put the active cell manually in A1. However, the first range select always give me the "1004" error. Why? I don't have any clue.
Range("a1:a5").Select
Do Until ActiveCell.Value = "stop"
Do Until IsEmpty(ActiveCell)
Selection.Offset(1, 0).Select
Loop
ActiveCell.Delete
Loop
I have been trying to use the activecell as a referance to delete the selected kit. _Though i need like + 2 columns to xlToRight.
Then i want to select the data below the deleted kit and cut paste it to the orignal activecell.
I can use Find to find all all my rows where there is an "X" in a cell, and delete all the rows found that way (even if they are separated by other rows) in one fell swoop -MANUALLY. I use Find after having limited the area to be searched with a named array (so other "X's" don't get involved).
But when I record a macro with all the same moves, NONE of the Find code appears in the macro AT ALL....just the delete command. Hello? Relative reference (on the record macro toolbar) seems to have no impact.
So....the mission here is to delete entire rows wherever an "X" has been entered in a certain cell to mark the row for deletion...and those X's get there either through a DV list OR by a cell below the DV copying down the X from the DV cell above.
This is so because sometimes rows are "sub" to the one above, and if the one above is marked for deletion, then so must be the rows sub to it.
I have found this seemingly simple for...next loop here on the board:
For a = 1 To 50
If Cells(a, 17) = "x" Then
Rows(a).Select
Selection.EntireRow.Delete
Next a
But the debugger reports a "next" without a "for" which is obviously there in dark blue as I suppose it should be.
If I could make this puppy work, I would sooner have it start from A2 and then go down from there to the last row -wherever that happens to be.
i have this macro that currently looks in column J and if a cell contains "Total" it deletes the entire row.
Now what i need is to check in same column but i need it to delete the entire row if any part of the sentence in a cell within column J contains "Main Total"
Sub Macro1()
'this deletes all rows if cell in column J says "total"
Dim DeleteValue2 As String
Dim rng2 As Range
Dim calcmode2 As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
i have data from D2:D10000
I Used data of first five rows e.g ("d2:d6") if in range ("d2:d6") any cell is blank then delete the all contents of the rows
e.g
d2 have data
d3 have data
d4, d5, d6 have no data then the contents of range ("d4:d6") should delete
I've got a sheet that I build from a weekly sheet. I can have 30 to 60 rows, but I always have 10 to 20 rows left over with data in column C, none in A, my last code is "Range("A1").End(xlDown).Offset(1, 0).Select" which takes me to the last cell in column A. Any code that I can then delete say 20 rows after that? I tried one code, but it wanted to delete everything below the cell selected which ran & ran.
"-" are blanks. Range moves the selected cell to A4. I want to delete row selected + 19 more.
-|A|B|C|D
1|Y|2|9|J|
2|R|1|8|N|
3|G|4|7|N|
4|-|-|L|-|
5|-|-|L|-|
I have 9 columns with various data and true/false checkboxes. Column 9 is labelled "Delete" and also contains a true/false checkbox. If column 9 is checked (True) and then a command button is pressed the corresponding row of data is deleted.what would the command button code be.
View 1 Replies View RelatedI 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 )
it's possible to select a group of cells, or a column, and then have specific words (and the colon), like Email: Tel: Fax: and URL: found and removed?
I've got a list of contacts one after the other in a column like this
John
Accountant
456, Hillside Drive, Hamley
Tel: 09189374884
Fax: 0284764654
Email: john@email.om
URL: ww.johntheaccountant.com
The number of lines per contact isn't always the same.
Can it be done without Macros?
I have an excel form with a command (submit) button that opens up Outlook when clicked. I am looking for a way to have this submit button disappear when the user selects a specific item in a drop down list to make sure they do not email the form when it is used for a promotion (Promotion would be selected in the drop down).
View 8 Replies View RelatedI want to import one worksheet from different workbooks to Master workbook. In the source workbook, I need to import only ' Code' sheet and in this code sheet , I need only 4 columns(A, E, F, and I ) to import. I also need cell C3and E3 in source worksheet to master workbook
I want to import like 'Code(1), Code(2), Code(3),, and so on. I don't want to import whole thing in the one sheet in the master workbook.
IN the master workbook, the header is on A3, so starting row will be row 4 in master workbook.
Codes below is for 'stacked' style, I guess.
[Code] .....
I'm trying to sum selected columns of a specific row in a spreadsheet. The row name is to be matched to the same name in a different sheet; the column headings are also found on that separate sheet. I've tried SUM using INDEX and MATCH, but it's not working (most recently I get #N/A). I'm trying to do this sum for each separate row, first through sixth, with varying columns for each row.
I'm not opposed to getting into VBA, but wanted to see if there was something that I missed with the regular formulas.
My most recent iteration is:
=SUM(INDEX('Current'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($B6,'Current'!$A$1:$A$16,FALSE)):INDEX('Curre nt'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($C$2,'Current'!$A$1:$A$16,FALSE)))
[Code] .........
I have a data sheet which I need to print everyday, I need to print Column A plus other individual columns on separate pages. For eg. Column A + B, Column A + C, Column A + D etc until the last column. Besides hiding and unhiding, is there any way to do it via vba? For eg, pop up to ask user which column to print?
View 6 Replies View RelatedDaily I am getting a file which have so many columns with different names. I used to select and move my required columns to separate place. For exp.
I have the file with various columns, such are.
Sales Qty, Year, Tax, Company Code, Discount Paid, Sales Amount, Company Name
The above column order are not constant, daily the order will changes. But we have to make the order as below
Company Name, Company Code, Year, Sales Qty, Sales Amount, Tax, Discount Paid
Like this we have so many columns in the file and we used to rearrange as required.
Any formula or any macro to avoid this manual work with error, so that i will save my time and free from error.
My boss wants me to create a macro to hide columns if there is a "X" on top of the column labels. The macro should be in toggle mode whereby the next pressing of the macro will unhide the hidden column.
There are more than 50 columns in my actual worksheet. The macro has to check column by column whether there is "X" marked on top and hide it if so.
The next pressing of the macro should then unhide all the hidden columns.
OK, I want to run a macro that will:
(1) go in to sheets 'A', 'B' and 'C'
(2) find all cells in row 1 that equal "1"
(3) copy the entire column and paste as values
I have a worksheet which contain a lot of data in it. I want only certain rows and columns to be printed when I click a button. Actually I am trying to print a report.
Suppose I have data across A10:M100. Lets say from columns A to D are permanent, if the cell colour of any cell from E to M is not Red then that is to be printed along with the contents in A to D. Since I have bunch of data in that sheet almost 10000 rows I separated data by giving them name. If it is possible to have an option to choose from before printing that would be much much better. Because even though there are many cells left uncoloured I don't want them to be in the print as they are belong to another table (I have given name for each table)
If all columns from E to M in row 20 are Red then that row should not appear in the print. If J20 is left uncoloured then it has to be printed along with the data from A20 to D20 ( which I said permanent). This colour I fill by a macro as when required only for the range E to M.
I would like to select a number of columns and then run a macro that would insert two columns after each of the selected columns.
Or is there perhaps a faster way than using a macro?
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 RelatedWhen I select multiple cells horizontally at the top are highlighted columns for those cells.
I would need to know the number of those columns (es. 5), "on the fly", without having to rely on eye every time. This number maybe displayed in a status bar or other window...
I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.
Some questions:
1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select".
2) Is there a way to select the workbook from were to copy the column without needing it to actually open?
3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?
I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.
I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...
So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..
I made my own small code but dont know how to make it act differently on selected columns....
I would select the entire matrix and then want the macro to run differently, taking into consideration the column names
[Code].....
I also need to add a vlookup formula for some column based on the column B's value...
Ex: This is for Col C
[Code] .....
So how do I write the code in such a way that the code acts on its own differently.
How can I adapt the following code to only show Columns; A, B, E, F and J from Sheet(3) into a Listbox called lbx_LiveAllocations?
As it stands, this code is only adding Column A. My only alternative has been to add all columns on the worksheet to the listbox, however there's a lot of unneeded information between.
Code:
Dim LR As Long
Dim ctrl As Object
Dim i As Long
Dim dic As Object
Dim arr As Variant
Set dic = CreateObject("Scripting.Dictionary")
[Code] ........
Erase arr
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.
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'm trying to make a sheet to calculate my taxes. I would like to keep tax rates from previous year(s), and have them applied based on which year I input in a cell..
Let say that in cell B1 I write year 2013
In cell G1 I have year 2013 and in cells G2:G7 I have tax rates from year 2013.
In cell H1 I have year 2014 and in cells H2:H7 tax rates from year 2014
In cell B17 to B20 I would like to make a formula that multiplies cell B11 or B12 with information found in either column G or H based on input in B1.
I have attached a spread sheet as example.
I know I can make a formula based on IF(B1=2013;G*;IF(B2=2014;H*;etc..
Anyways, the issue is that when I have some cells selected then use the text-to-columns, it works fine.. but when I select an entire column, it "acts a fool"...
I made this video to show the effect on my computer.