Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-17-2018, 09:40 AM
14spar15 14spar15 is offline Set Range based on selected rows. Windows 7 64bit Set Range based on selected rows. Office 2010 64bit
Advanced Beginner
Set Range based on selected rows.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default 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:
Sub DeleteBlankF2Rows()

Selection.AutoFilter
ActiveSheet.Range("$A$8:$Y$48").AutoFilter Field:=3, Criteria1:="="


Selection.Delete Shift:=xlUp
End Sub
Reply With Quote
  #2  
Old 11-17-2018, 05:43 PM
Logit Logit is offline Set Range based on selected rows. Windows 10 Set Range based on selected rows. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Reply With Quote
  #3  
Old 11-18-2018, 09:45 AM
14spar15 14spar15 is offline Set Range based on selected rows. Windows 7 64bit Set Range based on selected rows. Office 2010 64bit
Advanced Beginner
Set Range based on selected rows.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 11-18-2018, 10:32 AM
Logit Logit is offline Set Range based on selected rows. Windows 10 Set Range based on selected rows. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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
If you don't want to manually select the rows, but rather have it done automatically, use this macro :


Code:
Sub DelCRows()
    Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Reply With Quote
  #5  
Old 11-18-2018, 11:03 AM
14spar15 14spar15 is offline Set Range based on selected rows. Windows 7 64bit Set Range based on selected rows. Office 2010 64bit
Advanced Beginner
Set Range based on selected rows.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 11-18-2018, 11:36 AM
Logit Logit is offline Set Range based on selected rows. Windows 10 Set Range based on selected rows. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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).
Reply With Quote
  #7  
Old 11-18-2018, 01:15 PM
NoSparks NoSparks is offline Set Range based on selected rows. Windows 7 64bit Set Range based on selected rows. Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #8  
Old 11-19-2018, 04:47 AM
14spar15 14spar15 is offline Set Range based on selected rows. Windows 7 64bit Set Range based on selected rows. Office 2010 64bit
Advanced Beginner
Set Range based on selected rows.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Thanks guys...Appreciate the help here.
Reply With Quote
  #9  
Old 11-19-2018, 08:08 AM
Logit Logit is offline Set Range based on selected rows. Windows 10 Set Range based on selected rows. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

You are welcome.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Set Range based on selected rows. Conditional Formatting Individual Rows in a range Phil H Excel 3 11-05-2018 12:09 PM
Set Range based on selected rows. 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
Set Range based on selected rows. 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft