Macro to detect 1st and last row in range and delete rows in between.

[EBustamante]

I need some assistance with this macro.

Column B2:B26 has 25 rows to be filled in with text, occasionally it can only be filled with less, ex. 20 rows of text.

So the macro is executed, it will sort the 25 rows in ascending order A-Z, then copy and paste to range B36:B60, insert TRIM function to all 25 rows, this leaves only the first 2 letters of each last name then copy and paste rows as a value.

So then the macro will insert the text from b36 and b60 to the header at B1. So it changes from BUNDLE 1 to BUNDLE 1 AD THRU RY.

This macro work perfect when ALL 25 rows have text in them, but does not work as well when range has less then 25 rows of text. What I need is for a macro to detect the first and last row which contains text and delete the rows in between, this way it is picking up the lowest alpha and hight alpha characters in the column..


Sub SORTCOLUMNS()
'
' MESSAGE BOX WILL PROMPT USER TO STAR THE SORTING PROCESS OR NOT.
' IF YES, THEN WORKSHEET WILL BE SAVED AND SORT THROUGH ALL 50 COLUMNS BY CALLING BUNDLE# MACRO.
' IF NO, THEN STOP.

Sheets("NAMES").Select
Dim nResult As Long
nResult = MsgBox( _
Prompt:=" SORT COLUMNS NOW?", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
End If
'
' SAVES WORKBOOK BEFORE SORTING AND LABELING.
ActiveWorkbook.Save
'
Call BUNDLE1

End Sub

Sub BUNDLE1()
'
' TURN OFF SCREEN UPDATING.
Call SCREENOFFN
'
' SORT NAMES IN COLUMN B, RANGE B2:B26 WITH BLANK CELLS.
Sheets("NAMES").Select
Range("B1:B26").Select
ActiveWorkbook.Worksheets("NAMES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("NAMES").Sort.SortFields.Add Key:=Range("B2:B26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("NAMES").Sort
.SetRange Range("B1:B26")
.HEADER = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'
' INJECT TRIM MACRO TO RANGE OF CELLS & PASTE DATA TO RANGE B37:B60.
Range("B36").Select
ActiveCell.FormulaR1C1 = "=LEFT(TRIM(R[-34]C),2)"
Selection.Copy
Range("B37:B60").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'
' COPY LIVE FORMULA AND PASTE AS VALUE.
Range("B36:B60").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

'
' CREATE HEADER WITH BUNDLE # AND FIRST AND LAST LETTER.

Range("B1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = _
"=RIGHT("" BUNDLE 1 ""&R[35]C,17)&"" THRU ""&R[36]C"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
'
' Call BUNDLE2
'
End Sub

<colgroup><col style="mso-width-source:userset;mso-width-alt:5741;width:118pt" width="157"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:6546;width:134pt" width="179"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:6546;width:134pt" width="179"> </colgroup><tbody>
[TD="class: xl65, width: 157"]BUNDLE 1[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl65, width: 179"] BUNDLE 1 AD THRU BA[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl65, width: 179"] BUNDLE 1 AD THRU RY[/TD]

[TD="class: xl66"]ADAMS E J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]AD[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]ADAMS E J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]ADAMS E J [/TD]

[TD="class: xl66"]BARRY R J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]BA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]BARRY R J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]BARRY R J [/TD]

[TD="class: xl66"]CANFIELD R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]CA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]CANFIELD R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]CANFIELD R[/TD]

[TD="class: xl66"]DRAKE T[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]DR[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]DRAKE T[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]DRAKE T[/TD]

[TD="class: xl66"]FREEMAN ANTHONY C[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]FR[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]FREEMAN ANTHONY C[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]FREEMAN ANTHONY C[/TD]

[TD="class: xl66"]HAINES R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]HA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HAINES R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HAINES R[/TD]

[TD="class: xl66"]HAINLINE R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]HA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HAINLINE R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HAINLINE R[/TD]

[TD="class: xl66"]HUDNELL DEB[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]HU[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HUDNELL DEB[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HUDNELL DEB[/TD]

[TD="class: xl66"]HUDSON S D[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]HU[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HUDSON S D[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HUDSON S D[/TD]

[TD="class: xl66"]HYDER M[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]HY[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HYDER M[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]HYDER M[/TD]

[TD="class: xl66"]IRVIN J[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]IR[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]IRVIN J[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]IRVIN J[/TD]

[TD="class: xl66"]KATHRADA M H[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]KA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]KATHRADA M H[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]KATHRADA M H[/TD]

[TD="class: xl66"]KEARBY BRENDA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]KE[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]KEARBY BRENDA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]KEARBY BRENDA[/TD]

[TD="class: xl66"]KRAMBUHL CHARLES[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]KR[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]KRAMBUHL CHARLES[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]KRAMBUHL CHARLES[/TD]

[TD="class: xl66"]LIEBL C J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]LI[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]LIEBL C J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]LIEBL C J [/TD]

[TD="class: xl66"]MAGEE E[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]MA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]MAGEE E[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]MAGEE E[/TD]

[TD="class: xl66"]MCCALEB R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]MC[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]MCCALEB R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]MCCALEB R[/TD]

[TD="class: xl66"]MOFFATT CHRISTOPHER[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]MO[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]MOFFATT CHRISTOPHER[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]MOFFATT CHRISTOPHER[/TD]

[TD="class: xl66"]RAMIREZ R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]RA[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]RAMIREZ R[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]RAMIREZ R[/TD]

[TD="class: xl66"]RYAN T J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]RY[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]RYAN T J [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]RYAN T J [/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

</tbody>