Printable View
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
[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]
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
I only modified your code to be non-form code. Did it do all of that before?
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
Could you people help me in this regard?
Could anybody please help?
What do you mean by "you people" :wot
roflmao
any one who know the soluation
It seems to go unanswered.