Excel VBA How to select rows based on data in a column

Question

Sub SelectAllReleventText   Do While Range    A1     Offset 1  6   lt  gt  Empty Rows ActiveCell Row  Select ActiveCell Offset 1  0  Select Loop End Sub   Here is my script  I ve been told it doesn t do what it is meant to  which I expected since this was my first attempt  I am coming up with a variable not defined error  I thought I defined the  variable  but I guess it wasn t specific enough for Excel VBA    This is what I am attempting to do     In Workbook 1  On B6 there is an alphanumeric name  I want that row to be selected  Go down one row  if there is text there select that row  Continue till text is no longer prevalent   Copy selected rows  Paste into another workbook  Workbook2   into tab 1  starting on row 2  since row 1 has headers    Thanks in advance  Just a heads up  I am using the Options Explicit in my VBA because I was told it was the  right way to do thing

User · Answer

The easiest way to do it is to use the End method  which is gives you the cell that you reach by pressing the end key and then a direction when you re on a cell  in this case B6   This won t give you what you expect if B6 or B7 is empty  though   Dim start cell As Range Set start cell   Range   Workbook1 xlsx Sheet1 B6   Range start cell  start cell End xlDown   Copy Range   Workbook2 xlsx Sheet1 A2     If you can t use End  then you would have to use a loop   Dim start cell As Range  end cell As Range  Set start cell   Range   Workbook1 xlsx Sheet1 B6   Set end cell   start cell  Do Until IsEmpty end cell Offset 1  0       Set end cell   end cell Offset 1  0  Loop  Range start cell  end cell  Copy Range   Workbook2 xlsx Sheet1 A2

User · Answer

Yes using Option Explicit is a good habit  Using  Select however is not    it reduces the speed of the code  Also fully justify sheet names else the code will always run for the Activesheet which might not be what you actually wanted   Is this what you are trying   Option Explicit  Sub Sample       Dim lastRow As Long  i As Long     Dim CopyRange As Range          gt  Change Sheet1 to relevant sheet name     With Sheets  Sheet1           lastRow    Range  A   amp   Rows Count  End xlUp  Row          For i   2 To lastRow             If Len Trim  Range  A   amp  i  Value    lt  gt  0 Then                 If CopyRange Is Nothing Then                     Set CopyRange    Rows i                  Else                     Set CopyRange   Union CopyRange   Rows i                   End If             Else                 Exit For             End If         Next          If Not CopyRange Is Nothing Then                 gt  Change Sheet2 to relevant sheet name             CopyRange Copy Sheets  Sheet2   Rows 1          End If     End With End Sub   NOTE  If if you have data from Row 2 till Row 10 and row 11 is blank and then you have data again from Row 12 then the above code will only copy data from Row 2 till Row 10  If you want to copy all rows which have data then use this code   Option Explicit  Sub Sample       Dim lastRow As Long  i As Long     Dim CopyRange As Range          gt  Change Sheet1 to relevant sheet name     With Sheets  Sheet1           lastRow    Range  A   amp   Rows Count  End xlUp  Row          For i   2 To lastRow             If Len Trim  Range  A   amp  i  Value    lt  gt  0 Then                 If CopyRange Is Nothing Then                     Set CopyRange    Rows i                  Else                     Set CopyRange   Union CopyRange   Rows i                   End If             End If         Next          If Not CopyRange Is Nothing Then                 gt  Change Sheet2 to relevant sheet name             CopyRange Copy Sheets  Sheet2   Rows 1          End If     End With End Sub   Hope this is what you wanted   Sid

[excel] Excel VBA, How to select rows based on data in a column?

The answer is

Examples related to excel

Examples related to vba

Tags