Input box with dropdown list of all sheets

[Greg Snidow]

Greetings all. I am working on a macro to take an active row, cut it, then
insert it into a new location, sort the target, then return to the starting
sheet and delete the empty row. The target sheet is a user prompt, where the
user types in the name of the target sheet. I would like for the input box
to open with a dropdown list of all the sheets in the workbook. Also, I
found that in some places I could perform action on the target worksheet
without activating it, like Sheets(Target).Range.some action. But then for
others I have to activate it, so I just activated it from the start. Is
there a way to perform the actions on the target without activating it?
Below is the macro with some comments. I'm still learning, so there are
probably things I am doing that are not the best way to do it, but I try
everything until something works, then I stop. Thank you.

Greg

Sub MoveJobs()

'Get the name of the active sheet so I can come back to it
Dim ActSheet As String
Dim ActCell As String
ActCell = ActiveCell.Address
ActSheet = ActiveSheet.Name

'Get the name of the target sheet
'I would like the imput box to have a drop down menu of all the sheets
'so the user does not have to enter it
Dim Target As String
Target = InputBox("Move where?")

'Move the selection to the first data row in the target sheet
Dim SheetName As Variant
ActiveCell.EntireRow.Select
Selection.Cut
Sheets(Target).Rows("2:2").Insert Shift:=xlDown
lastrow = Sheets(Target).[B65000].End(xlUp).Row

'Delete any rows where column C is blank
'I tried this method to delete the cut row once back on
'the starting sheet, but sometimes it leaves rows.
'If I run it again, it will delete the row from last run, but
'not the new one. It works here though
Sheets(Target).Activate
LstRow = [C65000].End(xlUp).Row
Set MyRng = Range("C2:C" & LstRow)
For Each MyCell In MyRng
If MyCell.Value = "" Then
MyCell.EntireRow.Delete
End If
Next MyCell

'Reset the last row
LstRow = [C65000].End(xlUp).Row

'Resort the selection
Rows("2:" & LstRow).Sort Key1:=Range("B2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

'Go back to the starting sheet
Sheets(ActSheet).Activate

'Reset the last row
LstRow = [C65000].End(xlUp).Row

'Delete the blanks
'I tried this method on the target sheet, but it kept giving errors
'I can not figure out why it works in one place but not the other
'Basically I want one method that works everywhere, every time.
Range("C2:C" &
LstRow).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Return to the starting point
Range(ActCell).Select

End Sub

 

[OssieMac]

Hi Greg,

To find row number of last row use the following. It is generic irrespective
of the number of rows in the worksheet and will be forward compatable with
Excel 2007.

lastrow = Sheets(Target).Cells(Rows.Count, "B").End(xlUp).Row

When deleting rows you must work backwards from the bottom. For instance
when working forward if you want to delete rows 4 and 5. Delete row 4 and row
5 is now row 4. The next iteration is row 5 so new row 4 is skipped.

following is air code so forgive me if any errors but it will give you the
idea.

dim i as long

For i = MyRng.rows.count to 1 step -1
if cells(i,1) = "" Then
cells(i,1).entirerow.delete
end if
next i

 

[OssieMac]

Hi again Greg,

Had another look and this might work better.

LstRow = Cells(Rows.Count, "C").End(xlUp).Row
Set MyRng = Range("C2:C" & LstRow)

With MyRng
For i = .Rows.Count To 1 Step -1
If .Cells(i, 1) = "" Then
.Cells(i, 1).EntireRow.Delete
End If
Next i
End With

 

[Greg Snidow]

OssieMac, thank you for your help. Its still not deleting all the rows.
Does it matter if the adjecent cell has a value? I want to delete the entire
row if cell C is blank, even if there are values in other columns in the row.

 

[Greg Snidow]

I must apologize, it works fine. It was my brain that was not working
properly.