問題描述
從 VBA 自動排序中排除文本值 (Exclude Text Value From VBA Autosort)
I wonder whether someone may be able to help me please.
I'm using the code below to perform a sort upon before the workbook is closed.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Input").Protect "password", UserInterFaceOnly:=True
With ThisWorkbook.Worksheets("Input")
If .Range("B7").Value = "" Then Exit Sub
.Range("B7:AH400").Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
The code works fine, but I'd like to extend the functionality a little further and exclude the text value "Enter your name" from the sort, so the cell with this value in column B will always be the last row underneath all my data rows.
I've spent a good week or so on this trying to find a solution, but unfortunately I've been unable to do so. I've also looked at the Microsft site to see if their are any inbuilt functions in the 'Range.sort' method, but again I've drawn a blank on this.
I'm not even sure whether this is possible, but I just wondered whether someone could possibly look at this please and offer some guidance on how I may achieve this.
Many thanks and kind regards
Chris
‑‑‑‑‑
參考解法
方法 1:
Since the data you wish to exclude is at the bottom of the range you can simply "right size" the sort range to exclude it. use the .end and .row methods to figure out how much data you have
Dim EndRow as long
after the if statment add
'find last row of data
EndRow = Range("B7").end(xldown).row ‑1
the change the range to use your newly calculated end row.
.Range("B7:AH400")
becomes
.Range("B7:AH" & EndRow)