PDA

View Full Version : Need help to change codes



ayazgreat
11-19-2008, 12:08 PM
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
11-19-2008, 12:41 PM
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

ayazgreat
11-19-2008, 12:57 PM
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
11-19-2008, 01:01 PM
I only modified your code to be non-form code. Did it do all of that before?

ayazgreat
11-19-2008, 01:09 PM
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
11-20-2008, 04:22 AM
Could you people help me in this regard?

ayazgreat
11-20-2008, 01:04 PM
Could anybody please help?

Adonaioc
11-21-2008, 07:42 AM
What do you mean by "you people" :wot

roflmao

ayazgreat
11-23-2008, 12:52 AM
any one who know the soluation

ayazgreat
11-27-2008, 12:41 AM
It seems to go unanswered.