VBA Selection Code

[Prince Hussein]

Hello Mr Bill
Hello to the forum!

​

In VBA if i want to select the first row in selection or the last row or both : how to write the code, whatever the selection is : relative selection 9 different no of columns and different no of rows ) but with the same format : double border line in first row and the same in last row

​

I hope you have time to reply
Thanks

 

[Akinrotimi]

Hi Price Hussein,

If the 2 columns are A and B,use the following respective codes:

To select first row:
Range("a1:b1").End(xlDown).Offset(0, 0).EntireRow.Select

To select last row:
Range("a1000000:b1000000").End(xlUp).Offset(0, 0).EntireRow.Select

Regards

Rotimi

 

[shg]

Welcome to the board. Maybe ...

Sub x()
    With Selection.Areas(1)
        .Rows(1).Borders.LineStyle = xlDouble
        .Rows(.Rows.Count).Borders.LineStyle = xlDouble
    End With
End Sub

 

[Prince Hussein]

I do thank you for your instant kind response which is appreciated
the code is very great
but that is the formatting i need
<a href="http://www.flickr.com/photos/49060385@N07/6038769381/" title="Cell Borders by Prince Hussein, on Flickr"><img src="http://farm7.static.flickr.com/6146/6038769381_828801db05.jpg" width="500" height="313" alt="Cell Borders"></a>

Thanks

 

[shg]

Sub x()
    With Selection.Areas(1)
        .Borders.LineStyle = xlSingle
        .BorderAround LineStyle:=xlDouble
        .Rows(1).Borders.LineStyle = xlDouble
        .Rows(.Rows.Count).Borders.LineStyle = xlDouble
    End With
End Sub

 

[Prince Hussein]

I'm sorry but that is the result of the code and not the same as my image.
the code result image inside and around borders )

<a href="http://www.flickr.com/photos/49060385@N07/6039357233/" title="The code result by Prince Hussein, on Flickr"><img src="http://farm7.static.flickr.com/6129/6039357233_a99f6dd40c.jpg" width="500" height="317" alt="The code result"></a>

my image: ( inside and around borders )

<a href="http://www.flickr.com/photos/49060385@N07/6039356685/" title="cell borders required by Prince Hussein, on Flickr"><img src="http://farm7.static.flickr.com/6126/6039356685_4b7ee4d9c2.jpg" width="500" height="332" alt="cell borders required"></a>

sorry for disturbing you but I'm trying to learn Excel VBA and I don't have the tree of the objects and its' branches

Thanks

 

[shg]

Try recording a macro, Prince?

 

[hiker95]

Prince Hussein,


The following screenshot contains BOLD ranges inside YELLOW merged cells.


If we make a selection 4 columns wide x 8 rows deep, range L5:O12, and using Excel 2003, we get the following:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		B5:B7		D5:D8		F5:G7			I5:J8			L5:O12
4
5
6
7
8
9
10
11
12
13		B15:G24
14
15
16
17
18
19
20
21
22
23
24
25
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MakeSelectionBoarders()
' hiker95, 08/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=571562
Dim rf As Long, rl As Long, cf As Long, cl As Long
Application.ScreenUpdating = False

rf = Selection.Row
rl = rf + Selection.Rows.Count - 1
cf = Selection.Column
cl = cf + Selection.Columns.Count - 1

'All Cells
With Range(Cells(rf, cf), Cells(rl, cl))
  .Borders(xlEdgeLeft).Weight = xlMedium
  .Borders(xlEdgeTop).Weight = xlMedium
  .Borders(xlEdgeRight).Weight = xlMedium
  .Borders(xlEdgeBottom).Weight = xlMedium
  .Borders(xlInsideVertical).Weight = xlMedium
  .Borders(xlInsideHorizontal).Weight = xlMedium
End With

'First Row
With Range(Cells(rf, cf), Cells(rf, cl))
  .Borders(xlEdgeLeft).Weight = xlThick
  .Borders(xlEdgeTop).Weight = xlThick
  .Borders(xlEdgeRight).Weight = xlThick
  .Borders(xlEdgeBottom).Weight = xlThick
  .Borders(xlInsideVertical).Weight = xlThick
End With

'LastRow
With Range(Cells(rl, cf), Cells(rl, cl))
  .Borders(xlEdgeLeft).Weight = xlThick
  .Borders(xlEdgeTop).Weight = xlThick
  .Borders(xlEdgeRight).Weight = xlThick
  .Borders(xlEdgeBottom).Weight = xlThick
  .Borders(xlInsideVertical).Weight = xlThick
End With

'Inside Cells
With Range(Cells(rf + 1, cf), Cells(rl - 1, cl))
  '.Borders(xlEdgeLeft).Weight = xlThick
  '.Borders(xlEdgeTop).Weight = xlThick
  '.Borders(xlEdgeRight).Weight = xlThick
  '.Borders(xlEdgeBottom).Weight = xlThick
  .Borders(xlInsideVertical).Weight = xlThick
  .Borders(xlInsideHorizontal).Weight = xlMedium
End With

'First Column
With Range(Cells(rf, cf), Cells(rl, cf))
  .Borders(xlEdgeLeft).Weight = xlThick
End With

'Last Column
With Range(Cells(rf, cl), Cells(rl, cl))
  .Borders(xlEdgeRight).Weight = xlThick
End With

Application.ScreenUpdating = True
End Sub

Then make a selection of cells (not separated by blank rows or columns) and run the MakeSelectionBoarders macro:

 

[hiker95]

Prince Hussein,


You did not tell us what version of Excel you were using.


The above screenshots of the lines and boarders, and macro code, was written and tested with Excel 2003.


If you are using Excel 2007 and newer, let me know and I will test (make adjustments) with Excel 2007.

 

[Prince Hussein]

Hiker95,

Thanks a lot for your great effort , good done.
By the way ( I'm using Excel 2010 )

As an Expert ,I wonder if you have an advice for me or one two three,... steps I can follow to learn VBA quickly ( as I don't understand the tree of objects and its' branches like properties and methods , I can't follow it,you can say I ned a map, I need your help!!!! )

Have a nice time

Best Wishes

Hussein