MrExcel Publishing
Your One Stop for Excel Tips & Solutions

PS : Celia please read inside...


Posted by KID on May 03, 2000 5:05 AM

CELIA U HAVE WROTE THIS CODE 4 ME..N IT WORKS FINE..
I'M WONDERING..Set destName = Range("B65536").End(xlUp).Offset(1, 0)

B65536 ON ABOVE CODE..WHAT DOES IT MEANS..COULD U HELP XPLAIN ME
COZ I NEED 2 KNOW...THANKS A ZILLION


Option Explicit
Sub UpdateRankingMarkah()
Dim kidName As Range
Dim kidList As Range
Dim destName As Range
Set destName = Range("B65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False
Sheets("HIDE PKSR 10").Visible = True
Sheets("HIDE PKSR 10").Select
Set kidList = Range(Range("A3"), Range("A3") _
.End(xlDown)).Offset(0, 1)
For Each kidName In kidList
If kidName.Value <> "" Then
kidName.Copy
destName.PasteSpecial Paste:=xlValues
kidName.Offset(0, 14).Copy
destName.Offset(0, -1).PasteSpecial Paste:=xlValues
Set destName = destName.Offset(1, 0)
End If
Next kidName
Application.CutCopyMode = False
Sheets("RANKING MARKAH").Select
Range(Range("A5:B5"), Range("A5:B5").End(xlDown)) _
.Sort Key1:=Range("A5"), Order1:=xlAscending, _
Key2:=Range("B5"), Order2:=xlAscending, Header:=xlNo
Sheets("HIDE PKSR 10").Visible = False
Application.ScreenUpdating = False
End Sub



Posted by Celia on May 03, 2000 5:38 AM

Kid

Re :- Range("B65536").End(xlUp).Offset(1, 0)

-Row 65536 is the last row of an Excel worksheet.
-End(xlUp) does the same as pressing Ctrl+up arrow
-Offset(1, 0) moves the range ref down one row(1) but in the same column(0)

Therefore, the effect of these three bits of code joined together is to start at the last cell in column B, then move up to the last cell in col B that contains data, then move down one cell which puts us in the first blank cell of col B – which is the cell we were looking for.

There are other ways of getting there. It depends upon the situation.

Celia

Posted by KID on May 03, 2000 7:11 AM

thanks celia..but i still can't understand it..i will email you soon and hope you can spare your time to get me understand..hope you don't mind.thanks again