|
|
#1
11-17-2018, 09:40 AM
|
|||
|
|||
Set Range based on selected rows.
Hello, I must be using the wrong wording in my search for a solution here. I want my Range to be the highlighted rows. All I am finding is info on the Range based on a selected cell. Thanks
Quote:
|
|
#2
11-17-2018, 05:43 PM
|
|||
|
|||
|
This will autofilter any row/s ... cell/s you select. By "cell/s" I mean, you can select a single cell in row 14 but the entire row will be filtered viewable.
Code:
Sub SelFilterRange()
Dim crit() As Variant
Dim n As Long
Dim rgCell As Range
ReDim crit(1 To Selection.Count)
n = 1
For Each rgCell In Selection.Cells
crit(n) = CStr(rgCell.Value)
n = n + 1
Next rgCell
ActiveSheet.Range("$A$2:$AC$476").AutoFilter Field:=1, Criteria1:=crit, Operator:=xlFilterValues
End Sub
|
|
#3
11-18-2018, 09:45 AM
|
|||
|
|||
|
Thanks for the response here. Unfortunately this is not working for me and I’m trying to understand what you’re doing here so I can maybe modify it for my particular needs but I’m having no luck here. Basically what I need to do is this. After I select several rows in a sheet (they will all be grouped together if that matters) I run a macro that deletes any rows in that those selected rows that has a blank cell in column C. I can figure this out for doing the whole sheet or a predetermined range but I want the range to be whatever rows I select before running the macro. Thanks
|
|
#4
11-18-2018, 10:32 AM
|
|||
|
|||
|
.
You have indicated you are manually selecting the rows to be deleted. This macro will do that : Code:
Sub DelRows()
Selection.EntireRow.Delete
End Sub
Code:
Sub DelCRows()
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
|
|
#5
11-18-2018, 11:03 AM
|
|||
|
|||
|
Example - Rows 1-20 contain data. Cell C5 is blank and cell C15 is blank. If rows 10-20 are selected (highlighted) and the macro is run row 15 would be deleted because cell C15 is blank and it is in one of the selected (highlighted) rows. Row 5 is not deleted because it was not one of the initial selected (highlighted) rows. Part of the problem here is I don’t know that for sure “selected” and “highlighted” is one in the same. Also is this related to “Selection” in the coding terminology? Sorry I can explain this better.
|
|
#6
11-18-2018, 11:36 AM
|
|||
|
|||
|
.
The first macro I posted will hide the selected rows. It will not delete those rows. The second two macros posted both do the same thing - delete the rows selected. EXCEPT, "Selection.EntireRow.Delete" deletes rows period. There is no qualification such as an empty cell ... the macro just deletes any row that is selected. The other "Columns("C:C").SpecialCells(xlCellTypeBlanks).Ent ireRow.Delete" looks at the entire sheet automatically (no need to select any row/s), determines if there is a blank cell in the C column ... and if found, deletes that row. "Selection" is the same as you clicking on a row number (on the left side of the sheet).
|
|
#7
11-18-2018, 01:15 PM
|
|||
|
|||
|
a possible option
Code:
Sub Delete_Blank_C_Rows_From_Selection()
Dim rng As Range
Set rng = Intersect(Columns(3), Selection)
If Not rng Is Nothing Then
On Error Resume Next 'in case nothing to delete
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0 're-enable error notification
End If
End Sub
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
 Conditional Formatting Individual Rows in a range
|
Phil H | Excel | 3 | 11-05-2018 12:09 PM |
|
Import a specific range (bookmarked section) from all Word docs in a selected folder
|
Greengecko | Word VBA | 5 | 06-14-2016 07:54 AM |
| Find and Delete Rows based on a range | damaniam | Excel Programming | 2 | 03-12-2014 06:06 AM |
|
How to remove blank rows from a specified range?
|
Learner7 | Excel | 1 | 04-19-2011 02:45 AM |
| Temporarily show/hide selected table rows | glricht | Word Tables | 0 | 12-29-2009 05:40 AM |
Windows 7 64bit 
Office 2010 64bit
Linear Mode
