select column c from cell c22 till the last empty row

[BORUCH]

HI

i would like an excel VBA that does the following

i would like to select column c from cell C22 till the last empty cell in that column, then i also want to select the next column (column D from D22) whatever got selected in column c, (mirror image)

P.S My data set is filtered so i only want it to select on the filtered cells

THANKS

 

[Fluff]

Try

Range("C22", Range("C" & Rows.Count).End(xlUp).Offset(, 1)).SpecialCells(xlVisible).Select

 

[BORUCH]

Try

Range("C22", Range("C" & Rows.Count).End(xlUp).Offset(, 1)).SpecialCells(xlVisible).Select

thanks

i would like to add one more condition to this vba

the condition is that column C cell c22 it should say "ITEM #"

meaning to say that it only selects column c from cell C22 and the next column if in cell C22 it said the word "ITEM #"

so for ex. if the word "ITEM #" is displayed in cell B22 then it would select B22 till Last blank row and the next Column Column C


If THE WORD Item # doesn't exits at all in that row from A22 till.. it should display an error message that the word ITEM # is missing

 

[Fluff]

Try

Sub Chk1()
   Dim Fnd As Range
   Set Fnd = Rows(22).find("ITEM #", , , xlPart, , , False, , False)
   If Not Fnd Is Nothing Then
      Range(Fnd, Fnd.End(xlDown).Offset(, 1)).Select
   Else
      MsgBox "not found"
   End If

End Sub

 

[BORUCH]

Try

Sub Chk1()
   Dim Fnd As Range
   Set Fnd = Rows(22).find("ITEM #", , , xlPart, , , False, , False)
   If Not Fnd Is Nothing Then
      Range(Fnd, Fnd.End(xlDown).Offset(, 1)).Select
   Else
      MsgBox "not found"
   End If

End Sub

Hi

two problems

#1 it the word item # is a formula its coming form a different sheet (=sheet23!B22) the code has to modified to look at values

#2 if the word ITEM # exits in column B22 then it doesn't select till the last empty cell only till the first empty cell

can that be fixed ?

thanks

 

[Fluff]

How about

Sub Chk1()
   Dim Fnd As Range
   Set Fnd = Range("22:22").find("ITEM #", , xlValues, xlPart, , , False, , False)
   If Not Fnd Is Nothing Then
      Range(Fnd, Cells(Rows.Count, Fnd.Column).End(xlUp).Offset(, 1)).Select
   Else
      MsgBox "not found"
   End If

End Sub

 

[BORUCH]

actually i just realized that IN your last code it only selects till the first empty cell not till the last empty cell regardless of the word "ITEM #"

 

[BORUCH]

How about

Sub Chk1()
   Dim Fnd As Range
   Set Fnd = Range("22:22").find("ITEM #", , xlValues, xlPart, , , False, , False)
   If Not Fnd Is Nothing Then
      Range(Fnd, Cells(Rows.Count, Fnd.Column).End(xlUp).Offset(, 1)).Select
   Else
      MsgBox "not found"
   End If

End Sub

Thanks a million that works perfectly

 

[Fluff]

Glad to help & thanks for the feedback.