Posts by anson10x

    I have a spreadsheet that creates a report and writes the report into another worksheet. In order to carry out calculations it has to insert formulas into the worksheet. To do this, it needs a count of the number of lines in the active range, by that I mean the range that has text in the cells.


    Can someone please inform me how to count the number of lines in an active range?

    Re: Copy and Paste with Relative Cell References


    WillR:


    Thank you for the example. It works just fine.


    Something very strange is happening. When I delete the discrepancies worksheet, the macro would overwrite and get stuck at the same row. However, when I do a 'clear contents', I do not have a problem and the macro works by appending to the next row, just as it was intended to do.


    At any rate, now I know why the macro was not working for me at first and what to do to keep it working. Just curious, why does this happen when I delete rows as apposed to clearing contents?


    Thanks,


    Anson

    Re: Copy and Paste with Relative Cell References


    Every time I run the macro it writes to the same row, overwriting the last entry. Maybe a different approach is needed other than the one I was thinking with the variable.


    It appears that each time the macro is run, the lrow variable resets. Maybe there is now way to preserve it within the macro. Any suggestions, please?

    Re: Copy and Paste with Relative Cell References


    Jindon:


    To answer my own question, Rows(1) seems to place the copied text into Row 1. So all I have to do is insert a counter so that every time the paste is made the row is incremented. Maybe there is an easier way but, seems like this will work fine.

    Re: Copy and Paste with Relative Cell References


    Jindon:


    Your tip worked well. Here's the code I used:


    Sub copy_paste_row()


    With ActiveCell.EntireRow
    With .Interior
    .ColorIndex = 35
    .Pattern = X1solid
    .PatternColorIndex = x1Automatic
    End With
    .Copy Destination:=Sheets("discrepancies").Rows(1)
    End With
    Sheets("data").Select
    Selection.Offset(1, 0).Select

    End Sub


    However, I need to increment the "discrepancies" worksheed so that every time I run this macro, the it appends to the next row. Rows(1) seems to always add to the first row and overwrites. Any suggestions?


    BTW, this is a labor of love and I am not receiving any rewards other than personal satisfaction, unfortunately.


    Anson

    Re: Copy and Paste with Relative Cell References


    I need to copy a row and paste the row into another worksheet using relative cell references. I have tried the following code:


    Sub copy_paste_row()


    ActiveCell.EntireRow.Select
    Selection.Copy
    With Selection.Interior
    .ColorIndex = 35
    .Pattern = X1solid
    .PatternColorIndex = x1Automatic
    End with
    Sheets("discrepancies").Select
    Range("a1").Select
    ActiveSheet.Paste
    Application.CutCopyMode=False
    Sheets("data").Select
    Selection.offset(1,0).Select


    End Sub

    For some reason, ActiveSheet.Paste results in an error. Any clues, please?


    Moncks Corner, SC, USA

    Re: VBA ISNUMBER, ISTEXT functions


    When the entire column is blank or has text? Or if a cell in column a is blank/text it delet? I wanted to delete the entire row if the first cell is text, blank but not a number.


    Andy Pope: Many thanks for the code. It solved my problem and I'm on my way again.

    I am trying to create code that deletes lines in a spreadsheet when the first column is either blank or has text in it. However, when writing the VBA code, ISNUMBER and ISTEXT do not work. Amazingly, ISBLANK does work.


    Any clues will be greatley appreciated.