Need help to change codes

[ayazgreat]

Hi

I have a vba code workbook which I use to delete Series and copy to sheet, it is based on user form which opens asking for start end end range to delete series from worksheets,

I want that It is not based on user form but based on a sheet named TempRange where from column a (start range) to column b (end range) codes reads said columns ranges and perform the same as to be performed in my attached sheet.Kind see my attached sheet.


Thanks in advance
Ayazgreat

[Bob Phillips]

[vba]

Private Sub DeleteRanges()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long

With Sheets("Deleted Numbers")

dc = .Cells(2, Columns.Count).End(xlToLeft).Column - 1
dr = .Cells(Rows.Count, dc).End(xlUp).Row + 1
End With

If dr = 60001 Then
dr = 2
dc = dc + 2
End If

With Worksheets("TempRange")

lVal = .Range("A1").Value
uVal = .Range("B1").Value
End With

If lVal > uVal Then

MsgBox "End number must be greater than start number"
Exit Sub
End If

Application.StatusBar = "Deleting, please wait....!"
Application.ScreenUpdating = False

For i = 1 To Sheets.Count

With Sheets(i)

If .Name = "DATA" And _
.ProtectContents = False Then

Set Rng = .Range("A1", .Range("A1").SpecialCells(xlCellTypeLastCell))
For Each r In Rng

If r >= lVal And r <= uVal Then

With Sheets("Deleted Numbers")

.Cells(dr, dc).Value = r.Value
.Cells(dr, dc + 1).Value = Now
End With

If dr = 60000 Then

dr = 2
dc = dc + 2
Else

dr = dr + 1
End If

r.Clear
DeleteCount = DeleteCount + 1
End If
Next

On Error Resume Next
Rng.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
On Error GoTo 0
Set Rng = Nothing
End If
End With
Next

Application.ScreenUpdating = True

If DeleteCount = 0 Then

MsgBox "No Numbers Deleted"
Else

MsgBox DeleteCount & " numbers were deleted"
End If

Application.StatusBar = ""
End Sub
[/vba]

[ayazgreat]

Thank you very much Sir for you reply but your codes only delete numbers from row1 (column A, & B) I wish it reads all ranges in lst row in Column a and b as mentioned in attached file.

TempRange

[Bob Phillips]

I only modified your code to be non-form code. Did it do all of that before?

[ayazgreat]

No Sir it did not do all of that before but can it be done now ? It reads all ranges from column a & b at last row

Because it takes more time if I type one by one number in only row 1 in column a and b.

Temp Range

Start Range End Range
21000 21005
41001 41006
51002 51012
Thanks in advance

[ayazgreat]

Could you people help me in this regard?

[ayazgreat]

Could anybody please help?

[Adonaioc]

What do you mean by "you people"

roflmao

[ayazgreat]

any one who know the soluation

[ayazgreat]

It seems to go unanswered.