Solved: Deleting Rows if a particular string doesnt exist in it

[Ashes.cfg]

I am really the newest newbie in VBA with hardly any knowledge of the terminologies.
This is a very simple problem I have
I have to delete the entire row if "." doesnt exist in Column B.
Also please let me know how to activate a sheet if its name is something else than Sheet1 such that i can generalize it for all the excel sheets i have and add sheets thereafter!

The final product should be directly pasted in sheet 2!

Sub Delete()
Dim i As Integer, LastRow As Long
Worksheets("Sheet1").Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For i = 1 To LastRow

If IsEmpty(Cells(i, 2)) Then
Rows(i).Select
Selection.Delete Shift:=xlUp
End If
Next i
End Sub


This is what i ran for deleting empty rows!

[Norie]

Well for a start don't use Activate or Select/Selection.

If you want this code to work on all the sheets try something like this.

For Each ws In Worksheets
LastRow = ws.UsedRange.Rows.Count
For I = LastRow To 1 Step -1
If IsEmpty(ws.Cells(I,2)) Then ws.Cells(I,2).EntireRow.Delete
Next I
Next ws

[Ashes.cfg]

Well ok and how abt if i want to delete all the rows whose Column B do not contain the string "." ?? what is the function for tht?

[Bob Phillips]

Sub Delete()
Dim i As Integer, LastRow As Long
Worksheets("Sheet1").Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For i = LastRow to 1 Step -1

If Not Cells(i, 2).Value Like "*.*" Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub

[TonyJollans]

What about this?

Rows("1:1").Insert
Columns("B:B").AutoFilter Field:=1, Criteria1:="<>*.*"
Cells.Delete

[Ashes.cfg]

Hmm how abt if "." is just part of the string and is common with majority of the cells in column B. and these are the cells i wanna retain the remaining i wanna delete...I tried doing this but just not happening
Sub Delete()
Dim i As Integer, LastRow As Long
Sheets(1).Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
Const TEST_STRING As String = "."
For i = 1 To LastRow

If Not Right(Cells(i, "B").Value, Len(TEST_STRING)) = TEST_STRING Then
Rows(i).Select
Selection.Delete Shift:=xlUp
End If
Next i
End Sub


since . is just part of a huge string.. i want to retain all those strings containing . in them and delete the rest which dont ve . in them

pl help

[mdmackillop]

LastRow = ActiveSheet.UsedRange.Rows.Count

This will not return the correct value if you don't use Row 1
Try entering data in rows 8 and 20 of a new sheet and run this

Sub Test()
MsgBox ActiveSheet.UsedRange.Rows.Count
End Sub

[Ashes.cfg]

But how do i delete the rows which do not contain . in their string. and since . is just part of a huge string.. i want to retain all those strings containing . in them and delete the rest which dont ve . in them

[Bob Phillips]

Isn't that just what we gave you?

[Ashes.cfg]

ohh lol xld my bad :( didnt do it correctly.thanks topic solved lol!!! i am irritatingly funny :)