Vba select entire colum only used range

Question

Does anyone know how to select entire column but only used cells. Normally the data is continuous. One issue is that sometimes the sheet is filled only for two rows sometimes for hundreds.

What I need to obtain is to loop for all the files in the folder and copy recognized columns only with data. I cannot have selection of entire column because while pasting one below previous macro will throw an error due to range area not fitting.

The source data begins in different rows. Once it starts in second row the other time in third. There are no headers.

You probably don't *want* or *need* to `Select` anything. Avoid code that works off `Selection`, it's a macro-recorder thing that will plague your code with tons of bugs. — Mathieu Guindon, Feb 27 '17 at 21:04
Helpful question: [Error in finding last used cell in VBA](https://stackoverflow.com/q/11169445/4039286) — Fadi, Feb 27 '17 at 21:12
Yeah, I don't want use selection anywhere. Maybe I expressed myself not clearly. I need to copy column only where data is. As stated before data is populated sometimes from 2nd row or the other time from third. Taking data from E1 would copy empty rows which will later on throw an error. — Tommeck37, Feb 28 '17 at 16:17

score 0 · Answer 1 · edited Sep 08 '17 at 15:28

0

 MaxRow = Range("E" & Rows.Count).End(xlUp).Row

 Range("E1:E" & MaxRow).Select

Where E is your Column

edited Sep 08 '17 at 15:28

Sᴀᴍ Onᴇᴌᴀ

8,218
8
36
58

answered Feb 27 '17 at 20:58

sumeet singh kushwah

188
8

score 0 · Answer 2 · answered Sep 08 '17 at 15:03

I know this is old, but this is an easy way to select the data in a specific column and move it to a new location. It will remove all blanks in the range.

Columns("E:E").SpecialCells(xlCellTypeConstants).Copy Destination: = ActiveSheet.Range("F1")

Vba select entire colum only used range

2 Answers2