Select Specific rows from Excel using VBA

Question

I have a excel it contains many tabular and graphical data, from that I want to select only two rows and convert that to XML file using VBA. What I tried previous contains selection all rows and columns but I want to select row from 2 to 3 and column J to T. What I tried so far:

Sub FindUsedRange()
    Dim LastRow As Long
    Dim FirstRow As Long
    Dim LastCol As Integer
    Dim FirstCol As Integer

    ' Find the FIRST real row
    FirstRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByRows).Row
      
    ' Find the FIRST real column
    FirstCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByColumns).Column
    
    ' Find the LAST real row
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

    ' Find the LAST real column
    LastCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
        
'Select the ACTUAL Used Range as identified by the
'variables identified above
    'MsgBox (FirstRow & "," & LastRow & "," & FirstCol & "," & LastCol)
    Dim topCel As Range
    Dim bottomCel As Range
   
    Set topCel = Cells(FirstRow, FirstCol)
    Set bottomCel = Cells(LastRow, LastCol)
    
   ActiveSheet.Range(topCel, bottomCel).Select
End Sub

But it returns everything in the active sheet. I want to select from (J2 to T3), i.e. column J and row 2 to column T and row 3.Its fixed format so we can directly mention the row and col number.But i don't know how to change this function.

If you know the range then simply use Dim rng as range: Set rng = Range("J2:T3") — Siddharth Rout
– Siddharth Rout, Commented Sep 3, 2020 at 15:51

Tamil Selvan S · Accepted Answer · 2020-09-04 08:45:31Z

0

Dim cellvalue as range:Set cellvalue = Range("J2:T3")

It worked for me.

answered Sep 4, 2020 at 8:45

Tamil Selvan S

72612 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select Specific rows from Excel using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related