PDA

View Full Version : Solved: VBA: Find next empty cell in a certain range



Jarlisle
04-28-2009, 10:59 AM
The following code is used when I have some product numbers in column A and the corresponding Qty. in column B, but if there happens to be a product number in column A and nothing in B it will delete the column A contents. It works just fine unless there is only 1 number in column B and then the "Selection.End(xlDown).Select" statement moves too far down the sheet. I only want it to delete to row 37.

Application.ScreenUpdating = False
Range("A16:B37").Select
Range("B16").Activate
ActiveWorkbook.Worksheets("Wizard").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Wizard").Sort.SortFields.Add Key:=Range("B16"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Wizard").Sort
.SetRange Range("A16:B37")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B16").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(ActiveCell, "A37").ClearContents

Range("A16").Select

mdmackillop
04-28-2009, 11:41 AM
Can you post a sample to save us recreating your data?

Jarlisle
04-28-2009, 12:32 PM
Here's the example data

mdmackillop
04-28-2009, 12:52 PM
Work from the bottom up to find a "last" cell

Sub Macro1()
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B16"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A16:B37")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(Cells(38, 2).End(xlUp).Offset(1, -1), Cells(37, 1)).ClearContents
Application.ScreenUpdating = True
End Sub

Jarlisle
04-28-2009, 01:52 PM
Works great! Thanks!!!!