none
how to copy the data which are before the blank cell.. there might be data after the blank cell RRS feed

  • Question

  •  have written the code for getting the blank cell information but the problem is I WANT TO COPY THE DATA WHICH ARE BEFORE THE BLANK CELL.. AFTER THE BLANK CELL THERE MIGHT BE VALUE MY code is:

    Set rng = Range("6:6").Find(What:=horiz, LookIn:=xlValues, LookAt:=xlWhole)    
               
    If rng Is Nothing Then
             MsgBox
    "Value not found in row 1", vbExclamation
           
    Else
                 rng
    .EntireColumn.Copy
         Range
    ("p1").Select
         ActiveSheet
    .Paste
         Application
    .CutCopyMode = False
       MsgBox Range
    ("C1").End(xlDown)(2, 1).Value

    HELP ME

    Friday, September 14, 2012 11:24 AM

All replies

  • "Before" is a slippery concept: do you mean to the left? This gets everything from column A of the row where rng is found to the cell just to the left of the found cell:

    Set rng = rng.EntireRow.Cells(1,1).Resize(1, rng.Columns.Count -1)


    Friday, September 14, 2012 4:58 PM