Range selection specific column with End(xlDown)

[dwalc]

Hello, I'm encounter problem with VBA.

i want to select column A:D, H:J, M:P but with End(xlDown)
after that i want to find and replace the specific value in it like true to 1 and false to 0.

Dim AD As Long
Dim HJ As Long
With Sheets("Data")
AD = .Range("A2:D2").End(xlDown).Row
HJ = .Range("H2:J2").End(xlDown).Row
Selection.Replace What:="True", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With

any help would be really appreciated

 

[DanteAmor]

Try:

  Dim ad As Long, hj As Long, mp As Long
  
  With Sheets("Data")
     ad = .Range("A2:D2").End(xlDown).Row
    .Range("A2:D" & ad).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    hj = .Range("H2:J2").End(xlDown).Row
    .Range("H2:J" & hj).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    mp = .Range("M2:P2").End(xlDown).Row
    .Range("M2:P" & mp).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
  End With

 

[Alex Blakenburg]

i want to select column A:D, H:J, M:P but with End(xlDown)

Are you aware that using xlDown in that way only applies it to the first column in the range ie for A:D last row in column A, for H:J last row in column H ?
(actually its not necessarily the last row its more likely the last non-blank cell from the starting point)

 

[dwalc]

Try:

  Dim ad As Long, hj As Long, mp As Long
  
  With Sheets("Data")
     ad = .Range("A2:D2").End(xlDown).Row
    .Range("A2:D" & ad).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    hj = .Range("H2:J2").End(xlDown).Row
    .Range("H2:J" & hj).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    mp = .Range("M2:P2").End(xlDown).Row
    .Range("M2:P" & mp).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
  End With

thank you, it's works.

Are you aware that using xlDown in that way only applies it to the first column in the range ie for A:D last row in column A, for H:J last row in column H ?
(actually its not necessarily the last row its more likely the last non-blank cell from the starting point)

yup, i know. i want to select active column and row but have difficulty to write it in vba.

 

[Alex Blakenburg]

Typically there is a key data column that you can rely on as always being populated and you use that column to work out the Last Row.
Unless you have data underneath the data you are working on using xlUp is more reliable than xlDown,
You then use that Last Row number in the other ranges you are using.

If that is of interest I can give you an example using your ranges.

 

[dwalc]

please so. i would love to have an insight about it if it doesn't trouble you.

 

[Alex Blakenburg]

Ok using the solution code you have from Dante, see below.
The idea is that typically the data is a rectangle and you have a key column that is always going to be populated or will always have data on the last row of the rectangle.
So you find the last populated row in that column and use that as the last row for all the columns.

Sub ExampleLastRowUsing_xlUp_BasedOnDantesSolution()

  Dim LastRow As Long
  
  With Sheets("Data")
     LastRow = .Range("A" & Rows.Count).End(xlUp).Row
     
    .Range("A2:D" & LastRow).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    .Range("H2:J" & LastRow).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    .Range("M2:P" & LastRow).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
  End With

End Sub

 

[DanteAmor]

Complementing what Alex comments, and if the last row applies to all ranges, then you can simplify the code in this way:

Sub ExampleLastRowUsing_xlUp_BasedOnDantesSolution()
  Dim LastRow As Long
  
  With Sheets("Data")
     LastRow = .Range("A" & Rows.Count).End(xlUp).Row
     
    .Range("A2:D" & LastRow & ",H2:J" & LastRow & ",M2:P" & LastRow).Replace What:="True", _
      Replacement:="1", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:=False, ReplaceFormat:=False
      
  End With
End Sub

 

[dwalc]

Ok using the solution code you have from Dante, see below.
The idea is that typically the data is a rectangle and you have a key column that is always going to be populated or will always have data on the last row of the rectangle.
So you find the last populated row in that column and use that as the last row for all the columns.

Sub ExampleLastRowUsing_xlUp_BasedOnDantesSolution()

  Dim LastRow As Long
  
  With Sheets("Data")
     LastRow = .Range("A" & Rows.Count).End(xlUp).Row
     
    .Range("A2:D" & LastRow).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    .Range("H2:J" & LastRow).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
      
    .Range("M2:P" & LastRow).Replace What:="True", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
  End With

End Sub

I need to read it for a few times to understand the code, and the meaning behind it. I think I get it what you mean. it works great too. thank you, really appreciated it. and Dante simplifying it make it neater. thanks both of you.