View Full Version : [SOLVED] With...With end condition not working referencing a range

05-30-2015, 07:59 AM
Hey guys, one question

Does anyone found the same error than me with this condition? When im into one diferent activesheet from "Sammel-AEF", the code does not reference the rng2 var correctly and the routine fails. The countA does not understand that it has to look into the "Sammel-AEF" or the shtSammel columns and return its last used range...

strange...or wrong...

most probably, im wrong

Option Explicit

Sub dimw()
Dim shtSammel As Worksheet
Dim rng2 As range
Dim last_col As Integer
Set shtSammel = Worksheets("Sammel-AEF")
With shtSammel
If WorksheetFunction.CountA(cells) > 0 Then last_col = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Set rng2 = .range(.cells(1, 4), .cells(1, last_col))
End With

End Sub

05-30-2015, 08:34 AM
If WorksheetFunction.CountA(.cells) >

05-30-2015, 08:54 AM
If WorksheetFunction.CountA(.cells) >

Yep..SamT found it...thank you so much.