This should work.
Sub blnkSpec()
lstCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Range("A2", Cells(2, lstCol)).SpecialCells(xlCellTypeBlanks).Select
End Sub
It seems that when the Rows collection is used, it somehow tries to
incorporate the used range. I don't know why this occurs, but it did it
several times with me, and sometimes it ignored the specical cells altogether
and did nothing. But the code above worked every time. It sets a variable
range on row 2 that runs from A2 to cell in the last column with data on that
row. I think that is what you really wanted instead of the entire row's
blank cells.
"tina" wrote:
> Hi
> I tried both methods still not working . I know the cells are blank as the
> data imported is my data and i tried coping columns with data to new sheet
> i.e. columns a:da then tried still failed to make it even more confusing the
> formulas now works in first instance and allows me to enter data in first
> cell found but when repeat later to get next cell it fails I can get round
> this by range naming cell but I am confused why happening as this macro has
> been working fine for a couple of years
> Thanks for you time
> Tina
>
> "JLGWhiz" wrote:
>
> > Hi tina, I get mixed results, but if I use:
> >
> > Range("B2:H2").SpecialCells(xlBlanks).Select
> >
> > Then I get the right results for the blank cells.
> >
> > If I use Rows(2).Select, I get that row selected, but when I
> > use Rows("2:2").SpecialCells(xlBlanks).Select, it gives me
> > mixed results. Could be it don't like the Rows("2:2").
> >
> > "tina" wrote:
> >
> > > Hi
> > > I was using following code as part of macro which worked fine but now it
> > > errors as no blank cells I tried highlighting row 2 and selecting edit goto
> > > special blank cells states no cells . there are empty cells
> > > Rows("2:2").Select
> > > Selection.SpecialCells(xlBlanks).Select
> > > I have not made changes to spreadsheet layout just imported new data
> > > any Ideas
> > > Thank you
> > > Tina
|
Similar Threads
