Posts by skamat

    Re: Select Blank Cell in a Range


    I need to select the blank cell for formula.


    If that is not possible as the cells are blank then I have something in my mind that I am not able to figure it out how to go about would appreciate if helped.


    Idea is to select range D10 : I15


    My thought is


    1. Finding the number of columns from D8 to End of the row (In this case is I8). The number of counts would give 5 (column E8, F8,G8, H8, I8)


    2. Then counting number of rows from row number 10 to end from C10 to end (In this case the counts would be 5) (row 11, 12, 13, 14, 15)


    3. Now we can apply the formula


    Code
    set aa =  Range ("D10").offset(5)


    This will select cell D15 , As we know the number of rows is "5" so offset is 5, as per my idea in point 2


    Then another code


    Code
    set bb = range (aa.address).offset(, 5)


    Same here as we know the number of columns is "5" so offset is 5, as per my idea in point 1


    That should select cell I15


    This can be then put in my formula as below


    Code
    Range("D10:"  &  bb.address).select


    Hope I am able to express myself properly

    I want to select a Blank Cell in a range


    Please see the attached sheet. In the sheet I would like to select Cell I15.


    I am aware of selecting with the formula

    Code
    Range("C15").Offset(, 6).Select


    But what I need is to select with XL(end) formula.


    I have done some work that can be seen in the module, however I am even placing my requirments here.


    Re: copy and remove duplicates


    found a smaller code


    Code
    Sheet2.Range("C5", Sheet2.Range("C5").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("A1"), Unique:=True
            Sheet2.Range("A1", Sheet2.Range("A1").End(xlDown)).Cut
            Sheet1.Range("C7").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Sheet1.Range("C7:C" & Range("C" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo

    Re: copy and remove duplicates


    your code works perfectly fine with minor modification needed


    If you see sheet1 has letter "a" appearing 5 times and after your code is run the letter "a" appears 2 times in sheet2 instead of 1 time.


    I tried with different alphebets writing 2 times and same error occured it shows up 2 times in sheet2 instead of 1 time

    there is a long list of data in column C sheet1
    how can i copy that column to sheet2 column C by removing duplicate entries


    The code that I have is


    Code
    sheet1.Range("C5" & sheet1.Range("C" & Rows.Count).End(xlUp).Row).Copy
    sheet2.Range("C5").pastespecial
    sheet2.Range("C5:C" & Range("C" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo


    What happens is that if I am having 1500 or more entries, the entire 1500 entries is first copied and then duplicates removed.


    What I am looking for is a fast VBA code to copy but not duplicates so that 1500 entries are not copied but only single entries.
    The origional sheet1 entry should not be changed.

    Please see the sample sheet
    When you click on the command button the total is taken horizontally and vertically and written in their proper column.
    for Vertical total is taken from D7:D9 till the end of the column.
    For horizontal total is taken from D7:O7 for all 3 rows.


    If you now insert a row then the total is taken from D8:D10 where as I require from D7:D10
    Same if you insert a column.


    If you delete the column for formula should also change accordingly.


    The rows and columns changes frequently so the last row and column should be taken into account


    I hope the explanation is clear

    Re: sum start to end of the row


    Please see the sample sheet
    When you click on the command button the total is taken horizontally and vertically and written in their proper column.
    for Vertical total is taken from D7:D9 till the end of the column.
    For horizontal total is taken from D7:O7 for all 3 rows.


    If you now insert a row then the total is taken from D8:D10 where as I require from D7:D10
    Same if you insert a column.


    If you delete the column for formula should also change accordingly.


    The rows and columns changes frequently so the last row and column should be taken into account


    I hope the explanation is clear

    I have made a VBA code that calculates the total for the 3 rows.


    I am unable to rectify the code that calculates total from start to end this is because when the row increases the formula should also be adjusted accordingly.



    Code
    ws1.Range("D" & ws1.Cells(Rows.Count, "B").End(xlUp).Row + 2).Resize(, ws1.Cells(5, Columns.Count).End(xlToLeft).Column - 3).FormulaR1C1 = "=SUM(R[-4]C:R[-2]C)"
    ws1.Range("P7:P" & ws1.Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"

    Re: Remove Duplicates


    Your code works perfectly fine but small error pops up.


    I have created a macro wherein list of all items in particular month is copied.
    Wherever there are 2 or more items your code works fine. Even when no items is present the code works fine.


    But in certain cases where there are only 1 item debug error pops up.

    I have been searching the post for removing duplicates for long time but could not find it.


    Could anyone suggest only one line for removing all duplicate entries from column B7 to end of the line and adjusting the spaces accordingly.