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.
I 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 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'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|-|
The following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
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 RelatedMy 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 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 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 Relatedit'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?
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 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 have VBA code which selects a group of rows ranging between 1 row and 25 rows. I then want code to group the selected rows, however, the only code I know for grouping requires that I enter a specific starting and ending row (ExecuteExcel4Macro "SHOW.DETAIL(1,#,FALSE,#)").
Is there a way to have VBA group and hide a number or rows which is not known in advance and will change between daily executions of the code?
What I have just tried is click "Reset" in the macro menu and resaved the macro and the workbook, and the proper max/min lines seem to be working. However, I still feel a little unstable about it and will watch it closely.
(My next questions is how to "freeze pane" a row of headings on the results page and post the results onto row 2 and downward *without* deleted row 1)
Hello,
Here is my hurdle.
Could someone please look at the code below and see if there is a hint why all rows from the top all the way down (from R5 to R604) are outlined, when the code should successfully be stating already that if a value in the S column (to the right) is the maximum, then only outline the rows that across the R rows (up and down) that are the MIN? Then, those red-outlined rows should successfully display on the Results page.
It worked before, but I do not know now why all of the rows on the Details page are now selected.
Is there some indicator in the code that says outline everything?
Issue 2:
When I try to run the macro from a button I created, a pop up message says that the file is already open. If I take that literally, it is indeed true because I want the file open. Apparently Excel thinks I have 2 files open with the same title. It wants me to either close one of them or rename them, according to the message. But there isn't another file open according to my searching. What does that message really hint for me to do?
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View Relatedremoving duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
This works fine in detecting and copying the new material number accross to the master data sheet, but now i want it to copy the row in which the new material number is located as there is other information that goes with so it doesn't have to be manually typed in.
Dim MyValue As Variant
Dim FromSheet As Worksheet
Dim LookupColumn As Integer
Dim FromRow As Long
Dim FromColumn As Integer
'-
Dim ToSheet As Worksheet
Dim StartRow As Long
Dim LastRow As Long
Dim ActiveColumn As Integer
Dim ReturnColumnNumber
Dim ToRow As Long
Dim FoundCell As Object
'================================================= ============
'- MAIN ROUTINE
'================================================= ============
Sub DO_LOOKUP()
Application.Calculation = xlCalculationManual
'----------------------------------------------------------
'- LOOKUP SHEET [**AMEND AS REQUIRED**]
Set FromSheet = Workbooks("Book1.xls").Worksheets("MD")
LookupColumn = 2 ' look for match here
FromColumn = 2 ' return value from here
'-----------------------------------------------------------
'- ACTIVE SHEET
Set ToSheet = ActiveSheet
ActiveColumn = ActiveCell.Column................................
I think this might be a programming issue (not sure but I hope it can be solved).
I have three sheets, each containing a table. I'd like users to be able toggle or select rows from these tables for inclusion in a fourth table in a new worksheet.
Unfortunately I have zero knowledge of vba and 0.01 knowledge of macros. I'm attaching a sample of what I'm working with.
In the attached, the "price list," "likely to reference," and "optional" worksheets contain the source tables; I've added a column to the right of each called "include" (where I'd like a user to be able to select a desired row).
The "custom" worksheet is where I'd like an assembled worksheet to live.
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 have two workbooks say 'MASTER' & 'REVISED' in D:Office. The REVISED workbook contains certain rows colored in Yellow which contain the rectified data. How to copy those rows (rectified data rows of workbook REVISED) to the workbook MASTER in the same row number.
View 7 Replies View Relatedi would like to paste also the rows that selected from listbox i use a userform and also use multiselect listbox
Code:
Private Sub frmstartbtn_Click()
Dim lItem As Long
With Me.frmListBox1
[Code].....
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?
I’ve got a little problem with the text to column function.
I’ve got in column A critarias and if it is for example L it should run a surtain text to column code.
With Range("A1:A65536")
. AutoFilter Field:=1, Criteria1:="L"
Range("B1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).TextToColumnS Destination:=Range("B2"), DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, 1), Array(21, 1), Array(60, 1))
End With
With the Auto Filter some rows between 2 until 7624 are shown. But unfortunately the text to column code is used for every row between 2 and 7624. Does anyone know how to avoid it?
I need to copy only user selected rows ( multiple) from one sheet to another. I see similar threads here but none that work with user selected rows.
View 4 Replies View RelatedHow do you count know the numbers of rows that has a selection
View 2 Replies View RelatedIs 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 am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.