1

I have tried to select one range in Excel whose first column is filled with continuous data (10-20 rows) and in the range there can be empty cells. I recorded one macro but when I run this, it is not working.

Where is the mistake? 

'Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select`
pnuts
  • 58,317
  • 11
  • 87
  • 139
sisty
  • 23
  • 2
  • 5
  • 2
    Maybe elaborate what "*not working*" exactly means in this case. – Stefan Falk Aug 25 '15 at 13:27
  • the second selection.End(xlToRight).Select did not make the pass on right. Maybe because exists one empty cells. – sisty Aug 25 '15 at 13:29
  • 1
    You should add that to your question ^^ You can also address people by writing `@` e.g. @stefanfalk which sends a notification to the user which you want to talk to. – Stefan Falk Aug 25 '15 at 13:42
  • Is there a reason you need `.Select`? You're getting issues likely due to this alone. Can you expand on what you're trying to do? Almost guaranteed, there's an easier/better way to do it without using `.Select`. See [this thread](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) on ways to avoid `.Select`. Can you expand on your goal? I see you want to start in a column that has 10-20 rows of data. What if Col A has 9 continuous rows of data? Should we skip that, look to B? You really, really, should look into avoiding `.select`. – BruceWayne Aug 25 '15 at 16:17

3 Answers3

1

First there seems to be a typo in the code. At the end of the last statement you have a stray ` character.

What it seems like you want excel to do is the equivalent of CTRL+Shift+Down,Right,Right,Right. What the code is actually doing is Ctrl+[Arrow key] then expand the original selection to this new cell. Microsoft tells us that CTRL+[ArrowKey] brings us to the edge of the current region. As an illustration:

Ctrl Right

So since you have a range selected you will just be reselecting the same range every time!

What might be a solution for you is using the last column when trying to select ranges which require calling .End(xlToRight) multiple times:

'Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Cells(1,Selection.Columns.Count).End(xlToRight)).Select
Range(Selection, Selection.Cells(1,Selection.Columns.Count).End(xlToRight)).Select
Range(Selection, Selection.Cells(1,Selection.Columns.Count).End(xlToRight)).Select

Which is the equivalent to pressing CTRL+Shift+Down,Right,Right,Right.

Let me know if you have more problems :)

Michael S Priz
  • 1,116
  • 7
  • 17
  • It's true. I want CTRL+Shift+Down,Right,Right,Right. But the code don't execute the second Right because the next cell in first row is is empty. If you did in Excel _CTRL+Shift+Down,Right,Right,Right._ it's working perfect – sisty Aug 25 '15 at 14:23
  • @sisty Is the code I provided working? If not please add an explicit example of what is not working in your question. – Michael S Priz Aug 25 '15 at 14:24
0

You could try something like this (you may want to vary this a little)

Cells(ActiveCell.Row, 1000).Select
Selection.End(xlToLeft).Select
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, ActiveCell.Column)).Select
Range(Selection, Selection.End(xlDown)).Select

Instead of trying to keep going across until you hit the final filled cell - this starts off 1000 columns to the right then selects a range from column one to the last filled one (regardless of gaps)

I believe this would fit to the scenario mentioned - as it also would then go to the end of the filled selection.

You could however, adapt the code, to do the same thing for the rows.

Trum
  • 620
  • 3
  • 12
  • For example I had range "A2:F8". – sisty Aug 25 '15 at 13:56
  • I'm a little confused. What isn't working in particular? If you want to select range A2-F8, then that's just as simple as: Range("A2:F8").select The idea of this is that it ignores gaps when dynamically selecting, but I've only done this to the right for you - with a fairly large clue on how to do it down as well – Trum Aug 25 '15 at 14:01
  • I don't know the number of rows _(in my example is 8)_ wich will be selected with 'Range(Selection, Selection.End(xlDown)).Select' , but after this selection I want to select all until column F. – sisty Aug 25 '15 at 14:12
0

The table had empty cells in C2, E2

' Sub Macro2()

Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

Sheets("Sheet2").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select -Select first Column until exists data
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select -Did not make step over C2 (empty cell)
Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select


sourceCol = 1   
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

    For currentRow = 1 To rowCount + 1
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol).Select
    End If
Next

ActiveSheet.Paste
Sheets("Sheet2").Select
Range("A2:A100").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C2:C100").Select
Selection.ClearContents
ActiveWorkbook.Save
Range("A2").Select
End Sub'
sisty
  • 23
  • 2
  • 5