Issue with Used Range

[bachuwar.rajenderprasad]

Dear Friends,


I have the below code to create a textfile on my desktop with a used range.

When I ran the below macro for the first time used range was 805, then it was 600. But still the below code is using the range as 805 only.


Please suggest.


Sub Create_File()

Dim sFile As String

Dim sText As String

Dim iFileNum As Integer

Dim count_val As Long

count_val = Worksheets("Payments").UsedRange.Rows.Count

sFile = "C:Usersrajender_prasadDesktopTextfile.sql"

'MsgBox sText

iFileNum = FreeFile

Open sFile For Output As iFileNum

For i = 4 To count_val

sText = Worksheets("Payments").Range("N" & i).Value & ";"

Print #iFileNum, sText

'Print #iFileNum, "More text on next line"

'Print #iFileNum, "Etc."

Next

Close #iFileNum

MsgBox "file is created " & sFile

End Sub


Regards,

Prasad

 

[Hui]

Prasad


That sounds a bit odd


I would step through the code and check a few variables as you go along using the Watch or intermediate window

 

[Krishnakumar]

Hi


You should better to use


With Worksheets("Payments")

count_val = .Range("n" & .Rows.Count).End(3).Row

End With


Also this article might help you to understand Usedrange in detail.


http://www.excelfox.com/forum/f22/replacement-flawed-usedrange-property-374/


Kris

 

[Hui]

Kris


Thanx for that link


I'd never seen that described before

 

[bachuwar.rajenderprasad]

can some one pls explan in detail, as am new..

If possible please change in my existing code and post.


Thanks in advance.


prasad

 

[Marc L]

Hi Prasad !     Your problem remembers me another one "well known" …


So try to insert a new line like this :

[pre]

[…]
Dim count_val As Long
Worksheets("Payments").UsedRange
count_val = Worksheets("Payments").UsedRange.Rows.Count
[…]

[/pre]

Microsoft source :   http://support.microsoft.com/kb/163263/en-us


Regards,

Marc