Selecting cells with or without borders

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
I want to be able to select cells either with or without borders, irrespective of contents, it doesn't matter which, so I can isolate certain information with that selection and delete the rest of the sheet. I don't mind if it's done with a few clicks, or an add-in, or a macro, but it would be very helpful to me.

Thanks for looking at my question, I hope it makes sense.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
L

Legacy 98055

Guest
Try...

Code:
Sub Example()
    'selects all cells in the used range of the activesheet that do not contain a line
    FindByLine(UsedRange, xlNone).Select
    MsgBox "Continue..."
    'selects all cells in the used range of the activesheet that contain a solid line
    FindByLine(UsedRange, xlContinuous).Select
End Sub

Function FindByLine(SearchRange As Range, LineStyle As XlLineStyle) As Range
    Dim FirstCell As Range, NextCell As Range
    
    Application.FindFormat.Clear
    Application.FindFormat.Borders.LineStyle = LineStyle

    Set FirstCell = SearchRange.Find("", , , , , , , , True)
    If Not FirstCell Is Nothing Then
        Set FindByLine = FirstCell
        Set NextCell = FirstCell
        Do
            Set FindByLine = Union(FindByLine, NextCell)
            Debug.Print NextCell.Address, FindByLine.Address
            Set NextCell = SearchRange.Find("", NextCell, , , , , , , True)
        Loop While (Not NextCell Is Nothing) And (NextCell.Address <> FirstCell.Address)
    End If
End Function

Note that you can set a much more specific search format. See the "FindFormat" property in VBA help for an example. Also, use the macro recorder to see other examples...
 

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
That selects the area I want to delete and brings up the 'Continue?..'Box. When I click OK it selects the area I need to save. I really just need the first bit to select the non-bordered area so I can delete it
 
L

Legacy 98055

Guest
"I want to be able to select cells either with or without borders, irrespective of contents, it doesn't matter which..."

My code shows you how to do both. Remove the line that you don't need...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top