defing a range with a varying size...

[RobbieC]

Hi there, I need to define a range on a sheet in column A, eg. A1:A15 (15 values)

However, the number of values varies depending upon what has been inputted. Is there a way to define the range up to the last cell with a value and discount anything after?

If you can point me in the right direction, that's be brilliant. Thanks

 

[AkaTrouble]

the macro works for any sheet unchanged it only works on the currently active worksheet

so if you click sheet 7 tab and run the original macro it will work on sheet 7


if you want to be on sheet one but have code run on sheet 7 that can be done

 

[RobbieC]

Yes please. I need to run the code from different sheets than sheet7 - sheet7 is just where my data is being stored and isn't 'visible' and the contents are just referenced via other pages

Really appreciate your help Troub

 

[AkaTrouble]

ok try this you need to change with sheet name to the actual sheet name i have marked in red it will then only delete blanks from that sheet

Option Explicit
 
Sub DeleteBlanks()
    Dim intCol As Integer

With Worksheets("[COLOR=#ff0000]SheetName[/COLOR]")
     
    For intCol = 1 To 1 'cols A to change for number of columns
        Range(Cells(2, intCol), Cells(353, intCol)). _
        SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    Next intCol
    
End With
    
End Sub

 

[RobbieC]

hrm, I changed the code and changed the sheetname.

It didn't throw an error, but it didn't remove the blank cell. It does work tho if I'm on the active sheet... puzzling

 

[MARK858]

You need some periods/full stops

Sub DeleteBlanks()
    Dim intCol As Integer

With Worksheets("SheetName")
     
    For intCol = 1 To 1 'cols A to change for number of columns
        [COLOR="#FF0000"].[/COLOR]Range([COLOR="#FF0000"].[/COLOR]Cells(2, intCol),[COLOR="#FF0000"] .[/COLOR]Cells(353, intCol)). _
        SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    Next intCol
    
End With
    
End Sub

 

[RobbieC]

ha, BRILLIANT!

Thanks guys, that has done the trick - it works perfectly now!

Really appreciate your help - have a great weekend

 

[AkaTrouble]

You need some periods/full stops

Thank you