Code need fixing...........not amending if possible

Posted on 2007-08-07
Last Modified: 2013-11-05
Hi - i need to stop the code deleting the first four rows........just that.

Sub deleterows()
Dim row_count As Long
Dim check_col As Range
Dim req_column As String
Dim req_numeric As Integer
Dim isvalid As Boolean

    isvalid = False
    Do While Not isvalid
        req_column = InputBox("Please Enter Column for sorting against :", "Select Reference Column")
        If req_column = "" Then
            req_numeric = True
        ElseIf IsNumeric(req_column) Then
            req_numeric = CInt(req_column)
            req_numeric = col_convert(req_column)
        End If
        If req_numeric >= 0 And req_numeric <= 256 Then isvalid = True
    Set check_col = ActiveSheet.Cells(1, 1)
    For row_count = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        If Range("a1").Offset(row_count - 1, req_numeric - 1) = "" Then
'            Debug.Print "Delete row " & row_count
            Range("a1").Offset(row_count - 1, req_numeric - 1).EntireRow.Delete
        End If

End Sub
Function col_convert(req_column As String) As Integer
Dim charcount As Long
Dim temp_num As Long
Dim temp_string As String

    For charcount = 1 To Len(req_column)
        If Mid(req_column, charcount, 1) Like "[A-Za-z0-9]" Then temp_string = temp_string & Mid(req_column, charcount, 1)
    If Len(temp_string) > 2 Or Len(temp_string) = 0 Then
        col_convert = 0
    ElseIf Len(temp_string) = 1 Then
        col_convert = Asc(LCase(temp_string)) - 96
        temp_num = Asc(LCase(Right(temp_string, 1))) - 96 + (Asc(LCase(Left(temp_string, 1))) - 96) * 26
        col_convert = temp_num * Abs(temp_num <= 256)
    End If
End Function
Question by:keilah
    LVL 22

    Accepted Solution

    Change this:
    For row_count = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    to this:
    For row_count = ActiveSheet.UsedRange.Rows.Count To 5 Step -1

    Author Comment

    Hi i 'll try it and accept your solution...


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now