Excel 2013: Select all data but there's only 1 line

[pendle666]

Hello

In my report there is usually several lines of data and as part of my formatting macro I need to select all the info - no problem when there is more than one line - I use SHIFT - CTRL with the down arrow to select all the lines, but what if my report has only one line in it? Using the above selects all the blank lines too.

I know there is a way, but I'm damned if I can remember. Can someone enlighten me? This macro I'm working on just formats the report to the way I need it from data from another source which is copied and pasted, sometimes there's a lot of info, sometimes not.


thank you

Pendle

thank you for helping

____________
Pendle

 

[1726]

Select region at active cell expands current selection to select a range bordered on all sides with blank rows/columns - or edge of spreadsheet.

This will work with a single filled cell.

Keyboard: Ctrl *

However if you use the * on the main keyboard you may need to use:

keyboard: Shift Ctrl 8 - i.e. Shift 8 to get a *

This can also be accessed as an option in the Go To Special dialog box.

cheers

 

[SkipVought]

Hi,

Please post your code.

 

[pendle666]

many thanks

thank you for helping

____________
Pendle

 

[pendle666]

This is the macro

Basically the data is on 3 separate sheets. I've put below the section of the macro that copies the contents of sheets 2 and 3 into sheet 1. In this weeks' reports there were 2 lines in each, sometimes there is only 1.

Sub mileage1()
'
' mileage1 Macro
'
Range("A1:E2").Select
Range("E1").Activate
Selection.Copy
Sheets("Sheet1").Select
Selection.End(xlDown).Select
Range("A7").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A9").Select
Sheets("Sheet2").Select
Application.CutCopyMode = False
Range("A1:E2").Select
Range("E1").Activate
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Columns("A:A").Select
Selection.NumberFormat = "00000"
Columns("C").Select
Selection.NumberFormat = "0.00"
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A10") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:E10")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C7").Select
End Sub

thank you for helping

____________
Pendle

 

[SkipVought]

WHAT IF...
you did this with a query? Your QueryTable would be the report on sheet1, that would "merge" the data from sheet2 & sheet3 into one report, assuming that the structure on both sheets is identical AND the data on each sheet is a proper table containing ONE ROW of headings in row 1, with contiguous data and no other data on each sheet.

SQL:

Select *
From [Sheet2$]
Union All
Select *
From [Sheet3$]

Then all you'd do each week is simply refresh your QueryTable.

 

[SkipVought]

But here's what you could do with your macro.

Your code implies that you have no headings on sheets 2 & 3???

Sub mileage1()
    '
    ' mileage1 Macro
    '
    
    Sheets("sheet2").[A1].CurrentRegion.Copy
    
    Sheets("sheet1").[A1].End(xlDown).Offset(1).PasteSpecial xlPasteAll
    
    heets("sheet3").[A1].CurrentRegion.Copy
    
    Sheets("sheet1").[A1].End(xlDown).Offset(1).PasteSpecial xlPasteAll
    
    'your formatting follows here
End Sub