0
Sub Keeptop3()
  Dim lastrow As Integer
  lastrow = ActiveSheet.UsedRange.Rows.Count
  For i = 4 To lastrow
    Rows(i).Delete
  Next i
End Sub

I have used the lastrow to find the count of rows. I need only the top 3 rows. I am getting them plus the 5th row.

When I make it i=4, I get the top 2 rows and the 4th row.

What is wrong with my code?

DeanOC
  • 7,142
  • 6
  • 42
  • 56
RB17
  • 356
  • 1
  • 8
  • 26

3 Answers3

2

When deleting rows, it's a good idea to work backwards. Try this: 

Sub Keeptop3()
Dim lastrow As Integer
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = lastrow to 4 Step -1
    Rows(i).Delete
    Next i
End Sub
TMH8885
  • 888
  • 6
  • 15
0

If just want to keep the first 3 rows, (deleting all other regardless of their contents). Then just delete the range that goes from the 4th row to the last row, verifying that the last row is higher than 3. There is not need, if this is the case, to go one by one.

See code below (notice change of DataType for LastRow variable)

Sub Keeptop3()
Dim lastrow As Long
    With ActiveSheet
        lastrow = .UsedRange.SpecialCells(xlLastCell).Row
        If lastrow > 3 Then .Rows("4:" & lastrow).Delete
    End With
End Sub
EEM
  • 6,601
  • 2
  • 18
  • 33
-1

replace this:

lastrow = ActiveSheet.UsedRange.Rows.Count

with this:

lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
EEM
  • 6,601
  • 2
  • 18
  • 33
  • I would love to understand why the point down? This can only be considered not useful due to lack of knowledge. – EEM May 20 '15 at 23:15
  • There are two reasons I downvoted: 1) It doesn't solve the poster's problem, which is that their code skips rows when it deletes them. TMH8885's answer solves the problem by looping backwards. 2) As discussed around the internet, your method of finding the last row is problematic. Two better methods are to use something list `Range("A" & Activesheet.Rows.Count).End(XlUp).Row` or the `Find` function. See this question: http://stackoverflow.com/q/71180/293078 – Doug Glancy May 21 '15 at 01:03
  • Doug Glancy Thanks for your response: On 1: I would tend to agree. Mentioned that because the method originally used (also in TMH8885 answer) for finding the last row is inaccurate. Probably should had use a comment instead (but I just got that privilege today, not familiar with it). On 2: I have to disagree. Almost everything I read about being a problem (as you mentioned) comes out of misunderstanding of the fact that UsedRange is a range and not always starts from row 1, thus counting rows is inaccurate. – EEM May 21 '15 at 03:25
  • As per other two methods you mentioned Range("A" & Activesheet.Rows.Count).End(XlUp).Row Gives the last row not empty in column A (provided that the last row of column A is empty) not in the worksheet i.e. if the last cell with data in column A is A15 and in column E is E21. What about if the reference column is E but there is data in cell A22. Errors within the input data are not identified concurrently. I have done some research, in addition to what is in internet, as such if you are interested on the topic let's talk over the chat. Once again thanks for letting me know your reasons. – EEM May 21 '15 at 03:30
  • Thanks for the response.The problem with using the `UsedRange` is that sometimes it contains a lot of blanks at the bottom due to cells having been cleared and the workbook not yet saved. Your point about the last cell in A not necessarily equating to the last row can be true, of course, which is when you can use the Find method that I mentioned. Your suggestion of xlTypeLastCell was certainly an improvement over the original and I wouldn't have downvoted your post just for that. It was because the answer didn't address the problem. – Doug Glancy May 21 '15 at 05:10