Delete Selected Range
Mar 17, 2009
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
View 9 Replies
ADVERTISEMENT
Jul 25, 2014
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
View 1 Replies
View Related
Aug 12, 2014
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 Related
Aug 21, 2007
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
View 4 Replies
View Related
Aug 20, 2008
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.
View 9 Replies
View Related
Jul 17, 2009
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.
View 8 Replies
View Related
Feb 13, 2007
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.
View 9 Replies
View Related
Oct 23, 2009
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
View 9 Replies
View Related
Apr 15, 2014
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
View 3 Replies
View Related
Sep 24, 2011
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|-|
View 9 Replies
View Related
Feb 21, 2014
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 Related
May 20, 2009
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?
View 9 Replies
View Related
Mar 4, 2014
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 Related
Sep 16, 2009
I have a macro which I manually have to change the range in order to run the macro, e.g. Set rngData = Range("B4:I12")
rather than having to change the range for each macro, I was wondering if I could run the macro for the highlighted area. I have tried this, but doesn't seem to work.
Set rngData = Range. CurrentRegion.Select
View 2 Replies
View Related
Oct 5, 2009
I'm currently on Manual Calculation mode. I have formulas in cells A1 to A10 which has not been refreshed. Can I select only range A3 to A6 and recalculate only those cells?
View 5 Replies
View Related
Nov 15, 2009
I need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.
The original range changes at various times. Some times it might be C7: C452 etc.
I know it's easy, but I can get the code to keep the selection.
View 6 Replies
View Related
Mar 8, 2012
I have a range (of 2 rows) that are set up as a header row and sub row. I want a macro to insert these above the active row (ie. where the user places the cursor), but when I select and copy the range in the macro, I don't know how to refer back to the 'active row' because that's not active anymore.
I'd also like the cursor then to be placed into one of the cells in the new row, ready for the user to start editing.
View 6 Replies
View Related
Jul 2, 2013
I am looping through a selected range. The code searches through column B and finds any data. If found, it returns the value to a different workbook to column B. When there is no more data, it goes to column C and does the same thing. My problem is, when there is no data in a column it returns a blank. How do i get rid of this blank???
View 1 Replies
View Related
Mar 16, 2007
Count & Sum is one of the most usefull function in the Excel,
I always used this, Is it possible in VBA to create such code that if I Select a range and click on Commandbutton1 automatically one msgbox display with the Count & Sum of the Range Item
eg. If Range A2 to A5 has a number like 50,10,20,10
here if the command button is click automatically one msgbox is display
which showing the
-Total Sum of the Seletced Range is 100 and Total Count is 4
View 9 Replies
View Related
Aug 10, 2007
I'm trying to create an if statement that have 2 criterias. Here's what I've created:
=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)
This statement is returning the #VALUE error.
I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.
View 9 Replies
View Related
Jan 4, 2008
I am using the following code to Autofill a formula through a variable range:
Selection.AutoFill Destination:=Range(MyFormulaCell, TerminalCell), Type:=xlFillDefault
MyFormulaCell is the cell with the formula, and TermnialCell is a variable set on a count of rows. This is working great, and I can loop through this for the columns I need to copy just fine by incrementing values, but as I apply this to several sections, it's taking me up to a minute to run the macro. In this "I want it now" day and age, I'm sure my coworkers for whom I am making this will not be happy to wait that long.
Is there a way that I can incorporate a selected range into this method?
I'd like to set MyFormulaCell to:
Range(Selection, Selection.End(xlToRight)).Select
and then autofill the formula down the number of rows contained in TerminalCell.
View 9 Replies
View Related
Dec 3, 2008
I have a form that asks at what row the user wants to start with a selected range then how many rows to select. There will always be 21 columns selected with this range. I am still learning but it has to do with something on how I am setting the Start object. It is not being recognized.
Dim Row As Long
Dim Selection As Long
Dim Start As Range
Row = txtStartRow
Selection = txtHowMany
Set Start = Range("A" & txtStartRow).Select
Range(Start & ActiveCell.Offset(Selection, 20)).Select
View 9 Replies
View Related
Nov 29, 2006
I am trying to link data that has been entered into a inputbox with a range of cells.
My goal is to allow the user to answer: how many people sat the exam?
Therefore when a chart is automatically produced only the cells with the required data are selected.
So, my range is A2:C*
* being the user input.
View 4 Replies
View Related
Mar 15, 2007
I am trying to name a selected range in VBA in order to use it in a vlookup table. Code I have so far is as follows:
ActiveSheet.Name = TextN
Range("c9"). CurrentRegion.Select
Call formatting
ActiveWorkbook.Names.Add Name = "formTextN", RefersToR1C1 = Worksheets(TextN) & "!" & "r9c2:r45c13"
The formatting works, but the selection does not get named.
View 3 Replies
View Related
Dec 19, 2012
I have a spreadsheet with names of students, year groups, subjects and others. My VBA code will analyse student's results, but before I get there I would like the user to be able to select groups of students using listboxes.
Now I have a tonne of variables that I won't bore you with but the main thing I would like to do is to scan through each cell in a range on my spreadsheet and determine if the value of that cell is equal to a selected option in the listbox. This will let me, for example, filter by only Year 10 students, or select to display only Year 9 and 11 students.
I already have the code to scan through each cell:
VB:
'Run through each student and check they match the criteria
For N = 1 To StudentList.ListCount
'Split the first and second names
StudentName = Split(StudentList.List(N - 1))
[Code] .....
Essentially, I would like to take the cell "A" & N2 and check to see if it matches one of the selected options in a listbox called "YearList"
View 1 Replies
View Related
Apr 2, 2014
i have data in range c10:h100
i want if select a cell e.g = c15 then
highlight the range c10:c100 with color
now highlight the all row only highlight defined range
View 5 Replies
View Related
Apr 3, 2014
I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.
View 3 Replies
View Related
Apr 12, 2014
What I need is the following: I select a range of numbers and for this range I want to calculate the 1/3th and 2/3th percentile, and these should come in two cells that I can easily change in the vba code.
Some formula in which I can give two inputs: first the range of which it has to calculate the 1/3th and 2/3th percentile and a second input which is just a number and than the vba should say whether that second input is in the first range (between 0 and the 1/3th percentile value) or in the second range (between 1/3th and 2/3th percentile values) or third range (between2/3th percentile value and highest value).
View 1 Replies
View Related
May 8, 2014
I have the following code that selects a range based on the rows that have data in the sheet, starting with row 10:
[Code]....
This works well, but by default it captures all columns with data. I'm not sure how to then change it to only capture specific columns.
For example, if I only wanted columns B:D for rows with data, changing it to
[Code] .....
does not work, because the SpecialCells selection overrides that and still returns all columns with data.
Is there an easy way to drop column(s) off of the currently selected range after SpecialCells captures the range?
View 1 Replies
View Related
Jul 19, 2014
I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.
[Code] ........
Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'
'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.
View 6 Replies
View Related
