Link to home
Start Free TrialLog in
Avatar of keilah
keilahFlag for Netherlands

asked on

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

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)
        Else
            req_numeric = col_convert(req_column)
        End If
        If req_numeric >= 0 And req_numeric <= 256 Then isvalid = True
    Loop
    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
    Next

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)
    Next
    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
    Else
        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
ASKER CERTIFIED SOLUTION
Avatar of JimBrandley
JimBrandley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of keilah

ASKER

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

thanks.......