1

I have been able to successfully find the end of both a row and column, but I am only able to delete a column and not a row. The problem is that rows use numbers but my code is wanting a column letter. My goal is to delete everything after cell A5 to the right. Does anybody have any pointers?

Code:

Sub Range_End_Method()
'Finds the last non-blank cell in a single row or column

Dim lRow As Long
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveWorkbook.ActiveSheet.Range("A1:A" & lRow & "")
    .Clear
End With

Dim lCol As Long
    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

With ActiveWorkbook.ActiveSheet.Range("A1:???" & lCol & "")
   .Clear
End With

End Sub
  • 1
    This stack overflow explains converting a column number to letter. – Brian Mar 15 '19 at 17:26
  • 1
    First, unless you're doing more than .Clear in the With just use ActiveWorkbook.ActiveSheet.Range("A1:A" & lRow).Clear and note you do not need & "". For your ??? use Cells and two argument Range(<from>, <to>) like this ActiveWorkbook.ActiveSheet.Range("A1", Cells(1, lCol ).Clear . Cells takes either a column number or a string column letter as the column argument (the second argument). – Ted D. Mar 15 '19 at 17:56
  • It's not recommended to use Activeworkbook and/or Activesheet. ThisWorkbook.Sheets("MySheet1").Range("A1", Cells(1, lCol)).Clear – busybyte Mar 15 '19 at 18:09
  • @busybyte That won't work with dynamically named sheets, I am aware of that "rule" – FreeSoftwareServers Mar 15 '19 at 18:11
  • 2
    For dynamic case: set MySheet1=ThisWorkbook.Sheets(dynamic1) or store the new sheet-name in a string: dynamic1= Newname1. – busybyte Mar 15 '19 at 18:23
1

Update: I knew there was a way, but couldn't figure it out, but Ted.D & BusyByte helped get me there, to not convert the column number to a letter. This code is simpler and works:

Sub Clear()

Dim lCol As Long
    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
ActiveWorkbook.ActiveSheet.Range("E5", Cells(1, lCol )).Clear

End Sub

This finds the last used column as a number then converts it to a letter and then deletes all cells after first hard coded cell to the right (last used column).

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Sub Range_End_Method()
Dim lCol As Long
    'Find the last non-blank cell in row 1
    'but returns a NUMBER, that's what function Col_Letter converts to Letter
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

With ActiveWorkbook.ActiveSheet.Range("A5:" & Col_Letter(lCol) & "1")
   .Clear
End With

End Sub
|improve this answer|||||
  • 1
    correct the syntax error inherited from my comment by using a a double closing parenthesis Range("E5", Cells(1, lCol )).Clear and if you qualifying Range, you should qualify Cells as well. – Ted D. Mar 15 '19 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.