Excel VBA MsgBox

[amal1973]

Hello Developers..
I have a small problem! How can I Get the number of populated rows in a Msg Box. I have wrote a macro that will clean and move data to another sheet . I want to tell the user that he have successfully moved this amount of rows to the other sheet..
Thanks in advance

 

[JustinEzequiel]

nCount = 10

msgbox "you have successfully moved " & nCount & " rows to the other sheet"

 

[amal1973]

Thank you for your fast response, but the procedure exits when it just moves 10 rows . I want it to do is to give me a message of the total numbers of row moved from this work sheet to the other one ..

 

[JustinEzequiel]

[tt]nCount = #of rows[/tt]

if you don't know how many rows you moved,
post the code you use to move the rows.
hopefully, from your code,
we could help you determine
how many rows you moved

 

[amal1973]

Thats what i did write.. it works 100 very good. what is does ,it looks for textstrings . my only problem is how will i know the number of rows that have been populated in the other sheet (the rngdestination)


Sub CleanSheetTest()
Dim i As Integer
Dim rngdestination As Range
Dim Rng As Range
Dim j As Integer
Dim Wks As Worksheet
Dim Ncount As Integer
Dim Rng1 As Range

'Select the whole sheet and sorts it Ascending

Set Wks = Worksheets.Add
Wks.Name = "Summary"

Sheets("o_insurn&quot.Select
Cells.Select
Selection.Sort Key1:=Range("B1&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("B:B&quot.Select
Selection.TextToColumns Destination:=Range("B1&quot, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1)

'Setting the destination copy for the cells

Set rngdestination = Worksheets("Summary&quot.Range("A1&quot
For i = Cells(Rows.Count, "B&quot.End(xlUp).Row To 1 Step -1


If Left(Cells(i, "B&quot.Text, 8) = "AMERICAN" Then
Cells(i, "B&quot.EntireRow.Copy Destination:=rngdestination
Set rngdestination = rngdestination.Offset(rngdestination.Rows.Count)
Cells(i, "B&quot.EntireRow.Delete
End If

If Left(Cells(i, "B&quot.Text, 9) = "SUNAMERIC" Then
Cells(i, "B&quot.EntireRow.Copy Destination:=rngdestination
Set rngdestination = rngdestination.Offset(rngdestination.Rows.Count)
Cells(i, "B&quot.EntireRow.Delete
End If

If Left(Cells(i, "B&quot.Text, 8) = "HARTFORD" Then
Cells(i, "B&quot.EntireRow.Copy Destination:=rngdestination
Set rngdestination = rngdestination.Offset(rngdestination.Rows.Count)
Cells(i, "B&quot.EntireRow.Delete
End If

If Left(Cells(i, "B&quot.Text, 9) = "NATIONWID" Then
Cells(i, "B&quot.EntireRow.Copy Destination:=rngdestination
Set rngdestination = rngdestination.Offset(rngdestination.Rows.Count)
Cells(i, "B&quot.EntireRow.Delete
End If

If Cells(i, "C&quot.Value = 0 And Cells(i, "F&quot.Value = 20 Then
Rows(i).EntireRow.Delete
End If
Next i
'' this is my problem and it dont work
Set Rng1 = Worksheets("Summary&quot.Range(Selection, Selection.End(xlDown))

Ncount = Rng1.Count
MsgBox ("You have Successfully Transferred " & Ncount & " rows to the other sheet&quot

End Sub


thanks

 

[TopJack]

You already have the solution. Try this at the end .....

Ncount = Worksheets("Summary&quot.Cells(Rows.Count, "B&quot.End(xlUp).Row

Good Luck.

 

[amal1973]

It worked
Thanks a million