0

So i have this working but some days the data in Column P is blank then the "Last Column" stops at P when actually the last column could be S or T or what ever. Here is what i have so long:

Sub trledSCB() ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1 ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1, Criteria1:= _ "836553196" lastCol = ActiveSheet.Range("b4").End(xlToRight).Column Lastrow = ActiveSheet.Cells(4, 1).End(xlDown).Row ActiveSheet.Range("b4", ActiveSheet.Cells(Lastrow, lastCol)).Select End Sub

I hope you guys can help me here?

Improve this question
0

2 Answers 2

Reset to default
3

The problem seems to be how you are selecting your last column.

How you need to change it depends on what you want to count as your Last Column.

  • Do you want the last column within the Used range?
  • Even if that column only contains blanks?
  • Do you want the last column which contains a value within a specific row?

Try this:

Dim LastCell As Range Set LastCell = ActiveSheet.Cells.Find(What:="*", _ After:=ActiveSheet.Cells(1, 1), _ LookIn:=xlFormulas, _ LookAt:= xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False _ ) lastCol = LastCell.Column
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You can try this:

LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

More about it here: https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

This basically does same what your original line does but starting from right to left.

Sub trledSCB() ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1 ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1, Criteria1:= _ "836553196" lastCol = ActiveSheet.Cells(7, ActiveSheet.Columns.Count).End(xlToLeft).Column Lastrow = ActiveSheet.Cells(4, 2).End(xlDown).Row ActiveSheet.Range("b4", ActiveSheet.Cells(Lastrow, lastCol)).Select End Sub

By the way, using ActiveSheet is bad practice. Check this: How to avoid using Select in Excel VBA

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.